I never thought things would get so tricky with postgres dump and restore. Although it’s simple when done manually, but I was trying to automate the process of getting a dump from a remote server and restoring it locally. I was trying to use the “createdb” and “dropdb” command line tools to drop a db before I could restore the new dump – but with both the commands I wasn’t able to get around the password prompt. When I specified the -w (do not prompt for password) argument, the system doesn’t like it and throws an error.
dropdb: could not connect to database postgres: fe_sendauth: no password supplied
[Resolution: The user should be a superuser or owner of database in order to drop the datbase. I used this update “ALTER USER myuser WITH SUPERUSER;” to make the user a superuser and then use dropdb accountdb and createdb accountdb to drop and create the database respectively. After which I was able to restore the database from line 4 without any issue of having data conflicts]
So I had to finally use this technique to do this automated dump and restore process:
Note: .pgpass configuration is required to execute the following commands without password prompts.
1. Manually dump only the db schema from the source system [-w: without password prompt, -s: schema only]. This is a one time activity only.
pg_dump -hlocalhost -Uapiuser -ws accountdb > db_schema.sql
2. Manually create a database on the destination system with the necessary owner within the SQL prompt (assuming the user exists on the destination system)
CREATE DATABASE accountdb OWNER apiuser
3. Execute the following script on the source system to get the db dump [-w: without password prompt, -c: clean (drop) database objects prior to outputting the commands for creating them]
pg_dump -hlocalhost -Uapiuser -wc accountdb > automation_full.sql
4. Get the dump sql file locally on the destination system and restore the dump using the following command [-d: destination database, -f: input dump file]
psql -hlocalhost -Uapiuser -d accountdb -f automation_full.sql
TaDa.. Works like a charm! Try it out.