Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL: Backup and restore pg_dump with password on command line #sql
# best practice: linux
nano ~/.pgpass
*:5432:*:username:password
chmod 0600 ~/.pgpass
# best practice: windows
edit %APPDATA%\postgresql\pgpass.conf
*:5432:*:username:password
# linux
PGPASSWORD="password" pg_dump --no-owner -h host -p port -U username database > file.sql
# windows
PGPASSWORD=password&& pg_dump --no-owner -h host -p port -U username database > file.sql
# alternative
pg_dump --no-owner --dbname=postgresql://username:password@host:port/database > file.sql
# restore
psql --set ON_ERROR_STOP=on -U postgres database < file.sql
# backup exluding table
pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql
# backup including table
pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=foo database > tmp.sql
# backup and restore
PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
psql -U postgres -d database -c "drop schema public cascade; create schema public;"
psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f tmp.sql
rm tmp.sql
@aleh-rudzko

This comment has been minimized.

Copy link

aleh-rudzko commented Dec 28, 2017

thanks

@kholioeg

This comment has been minimized.

Copy link

kholioeg commented Mar 13, 2018

+1

@jometho

This comment has been minimized.

Copy link

jometho commented Mar 21, 2018

Restore and STOP in case of errors
psql --set ON_ERROR_STOP=on -h [host] -p [port] -U [user] -1 [restore_db]< [db_dump]

@ikunyemingor

This comment has been minimized.

Copy link

ikunyemingor commented Sep 12, 2018

+1

@marcospy

This comment has been minimized.

Copy link

marcospy commented Oct 24, 2018

Very helpful! Thanks!

@blacksmoke26

This comment has been minimized.

Copy link

blacksmoke26 commented Dec 12, 2018

+1

@aleon1220

This comment has been minimized.

Copy link

aleon1220 commented Jan 9, 2019

what is the point of ~/.pgpass if you are setting the pass as a value such as in PGPASSWORD="password"
?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.