Skip to content

Instantly share code, notes, and snippets.

Embed
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
*: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 excluding table
pg_dump --no-owner -h 127.0.0.1 -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 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 database > tmp.sql
# backup only schema (no data)
pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --schema-only database > tmp.sql
# transfer database with 2 tables empty
pg_dump --no-owner -h 127.0.0.1 -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 127.0.0.1 -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 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
@kvthangaraj
Copy link

kvthangaraj commented Feb 26, 2020

Thanks. It's working fine.

@amitk
Copy link

amitk commented Apr 8, 2020

thanks.

@dariogomez
Copy link

dariogomez commented Apr 23, 2020

thanks!

@yoyox20
Copy link

yoyox20 commented May 6, 2020

thankss I love u man hah,solve my problem

@anumber8
Copy link

anumber8 commented May 19, 2020

+1

@VictorZZZZ
Copy link

VictorZZZZ commented Sep 7, 2020

+1!!!!

@arulrajnet
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

@Moreless91
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