Postgresql – simple script to backup PostgreSQL database

batchdatabase-backuppostgresqlscripting

Hello I write simple batch script to backup postgeSQL databases, but I find one strange problem whether the pg_dump command can specify a password?

There is batch script:

 REM script to backup PostgresSQL databases
 @ECHO off

 FOR /f "tokens=1-4 delims=/ " %%i IN ("%date%") DO (

 SET dow=%%i
 SET month=%%j
 SET day=%%k
 SET year=%%l
 )

 SET datestr=%month%_%day%_%year%
 SET db1=opennms
 SET db2=postgres
 SET db3=sr_preproduction
 REM SET db4=sr_production

 ECHO datestr is %datestr%

 SET BACKUP_FILE1=D:\%db1%_%datestr%.sql
 SET FIlLENAME1=%db1%_%datestr%.sql

 SET BACKUP_FILE2=D:\%db2%_%datestr%.sql
 SET FIlLENAME2=%db2%_%datestr%.sql

 SET BACKUP_FILE3=D:\%db3%_%datestr%.sql
 SET FIlLENAME3=%db3%_%datestr%.sql

 SET BACKUP_FILE4=D:\%db14%_%datestr%.sql
 SET FIlLENAME4=%db4%_%datestr%.sql

 ECHO Backup file name is %FIlLENAME1% , %FIlLENAME2% , %FIlLENAME3% , %FIlLENAME4%

 ECHO off
 pg_dump -U postgres -h localhost -p 5432 %db1%  > %BACKUP_FILE1%
 pg_dump -U postgres -h localhost -p 5432 %db2%  > %BACKUP_FILE2%
 pg_dump -U postgres -h localhost -p 5432 %db3%  > %BACKUP_FILE3%
 REM pg_dump -U postgres -h localhost -p 5432 %db4%  > %BACKUP_FILE4%

 ECHO DONE !

Please give me advice

Regards Mick

Best Answer

You cannot specify a password for Postgres utilities like pg_dump on the command line.
This would be a giant security hole, requiring disgusting workarounds to mitigate.

What you CAN do is create a pgpass file, which pg_dump will consult.

Depending on your needs you may want to consider taking a filesystem level backup instead of using pg_dump -- pg_dump will need to lock various parts of your database while it runs, and may interrupt normal use of the system. Alternately, consider running your backup process on a slave system.

Related Solutions

Postgresql – Recover PostgreSQL database from filesystem backup

Please be aware that in PostgreSQL, your cluster data directory is a self-contained unit that cannot reliably be restored in parts, as per the documentation here.

Your best hope is to use the full data directory of the old server and to start a server on it, then restore from a dump of the database you get from there:

Get a postgresql.conf that fit the configuration on the new host going (orient yourself by the files in the old - which you change here - and new data directories)
Find out the user name of the old installation's PostgreSQL superuser - most likely postgres.
Create this user name (UNIX user) on the new host, unless it's already there.
Give (chown) the old data directory to that user, unless the uid matches the one on the old server.
Configure pg_hba.conf in the old data directory to allow connections from local users to the same postgres user name (this is normally the default, but check).
Run the old database cluster as the UNIX user with the same name as the old data directory's superuser, e.g. for user postgres:
```
sudo -u postgres postgres -D old_data_directory

or with su:

su postgres
postgres -D old_data_directory
```

dump the old database, again with the usual user name, postgres:

sudo -u postgres pg_dump -p /path/to/socket/for/old/directory database_name > dump_file.sql

becoming postgres with su analogous to above

adapt the dump_file.sql to the new server's user names etc; create the empty database with appropriate ownership there

scan the database into the new server:

sudo -u postgres psql -p /path/to/socket/for/new/directory new_database_name

once again, getting postgres privileges using su analogous to above

That should get you set, or at least show a good path to get there.

Postgresql – Can’t restore PostgreSQL database backup

I solved this problem with the following steps:

pg_restore -f db1.sql-v db1.tar

I then removed everything from the db1.sql file except for the table1 copy command. Then ran:

psql -d db1 < db1.sql

This then gave me the exact line number within the file where the error was occurring. I then opened the file and removed the problem character and re-ran the script.

Best Answer

Related Solutions

Postgresql – Recover PostgreSQL database from filesystem backup

or with su:

becoming postgres with su analogous to above

once again, getting postgres privileges using su analogous to above

Postgresql – Can’t restore PostgreSQL database backup

Related Topic