Blog Post

PostgreSQL Backup Failure

Microsoft SQL Server is an RDBMS I have always preferred, and loved to work on. All my life, even now-a-days as well. However, past couple of years I have had chance to work on a few more database management systems. Structured and Non-Structured both. Here after, you may see a few more posts for these types of database management systems as well. In this post today, I am going to write about the PostgreSQL backup failure.

This very issue I will discussing is really very interesting, at least for me. One of our product is running on PostgreSQL. And, I was working on automating back up. I have found a really very good batch script written by CodeMonk on the GitHub.

While I was testing the script on my local machine. It threw an error. Now, this error it reported was totally different than what it was actually. This is why it motivate me to write a blog post on it. By the way, our product runs on Windows Platform and is developed using MVC. While I executed the backup batch file it threw below error

pg_dump: [tar archiver] could not open TOC file "C:Backup&TryTest_backup__12/01/2023_Fri_12_00.tar" for output: No such file or directory

Since this was new to me, I tried myself and did some research. All my findings were redirecting me towards permission issue. I end up reviewing NTFS permission but this did not helped me at all. Finally, it catches my attention that the backup file name contains the forward slash in the name which is not allowed.

I read the script again and find that there is a parameter at the line number 50 in this script that generate the date and time stamp, and it picks up the value from the system setting.

set datestr=backup_%year%_%month%_%day%_%hour%_%min%

Fixing the PostgreSQL backup failure issue

And, now I understood where the problem is. Here is how I fixed the issue:

  1. Open Control Panel
  2. Regional Setting
  3. and change the Short and Long Date format

Once these settings have changed I re-run the backup batch file. And, voila! This time the backup is completed successfully.

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables

Last month, I have completed the a series on Common Mistakes in SQL Server. This series covers the aspects or configurations which are most common and often not considered at the time of designing database or development phase. I would suggest you to read them. Here is the link of the blog roll.

The post PostgreSQL Backup Failure appeared first on SQL Server Citation.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating