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 -d database -1 -f file.sql
pg_restore --no-privileges --no-owner -U postgres -d database --clean file.sql # only works for special dumps
# 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
# if you need to remove unintentionally backuped owners afterwards
sed -i.bak '/OWNER TO specialowner/d' input.sql
@aleh-rudzko

This comment has been minimized.

Copy link

@aleh-rudzko aleh-rudzko commented Dec 28, 2017

thanks

@kholioeg

This comment has been minimized.

Copy link

@kholioeg kholioeg commented Mar 13, 2018

+1

@jometho

This comment has been minimized.

Copy link

@jometho 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 ikunyemingor commented Sep 12, 2018

+1

@marcospy

This comment has been minimized.

Copy link

@marcospy marcospy commented Oct 24, 2018

Very helpful! Thanks!

@blacksmoke26

This comment has been minimized.

Copy link

@blacksmoke26 blacksmoke26 commented Dec 12, 2018

+1

@aleon1220

This comment has been minimized.

Copy link

@aleon1220 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"
?

@kvthangaraj

This comment has been minimized.

Copy link

@kvthangaraj kvthangaraj commented Feb 26, 2020

Thanks. It's working fine.

@amitk

This comment has been minimized.

Copy link

@amitk amitk commented Apr 8, 2020

thanks.

@dariogomez

This comment has been minimized.

Copy link

@dariogomez dariogomez commented Apr 23, 2020

thanks!

@yoyox20

This comment has been minimized.

Copy link

@yoyox20 yoyox20 commented May 6, 2020

thankss I love u man hah,solve my problem

@anumber8

This comment has been minimized.

Copy link

@anumber8 anumber8 commented May 19, 2020

+1

@VictorZZZZ

This comment has been minimized.

Copy link

@VictorZZZZ VictorZZZZ commented Sep 7, 2020

+1!!!!

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.