Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
PostgreSQL: Backup and restore export import pg_dump with password on command line #sql
# best practice: linux
nano ~/.pgpass
chmod 0600 ~/.pgpass
# best practice: windows
edit %APPDATA%\postgresql\pgpass.conf
# 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 excluding table
pg_dump --no-owner -h -p 5432 -U username --exclude-table=table1 --exclude-table=table2 --exclude-table=table1_id_seq --exclude-table=table2_id_seq database > tmp.sql
# backup including only table
pg_dump --no-owner -h -p 5432 -U username --table=table1 --table=table2 database > tmp.sql
# backup only schema (no data)
pg_dump --no-owner -h -p 5432 -U username --schema-only database > tmp.sql
# transfer database with 2 tables empty
pg_dump --no-owner -h -p 5432 -U username --exclude-table=table1 --exclude-table=table2 --exclude-table=table1_id_seq --exclude-table=table2_id_seq database > 1.sql
pg_dump --no-owner -h -p 5432 -U username --table=table1 --table=table2 --schema-only database > 2.sql
psql --set ON_ERROR_STOP=on -U username -d database -1 -f 1.sql
psql --set ON_ERROR_STOP=on -U username -d database -1 -f 2.sql
# backup and restore
PGPASSWORD=password && pg_dump --no-owner -h -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
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"

Copy link

kvthangaraj commented Feb 26, 2020

Thanks. It's working fine.

Copy link

amitk commented Apr 8, 2020


Copy link

dariogomez commented Apr 23, 2020


Copy link

yoyox20 commented May 6, 2020

thankss I love u man hah,solve my problem

Copy link

anumber8 commented May 19, 2020


Copy link

VictorZZZZ commented Sep 7, 2020


Copy link

arulrajnet commented Nov 18, 2020

To restore

pg_restore --no-privileges --no-owner --dbname=postgresql://username:password@host:port/database file.sql

You can give the password inline. It won't prompt

Copy link

Moreless91 commented Jun 11, 2022

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment