-
-
Save carpincho/9ae5e4f72f4e66a38358e16a965222b1 to your computer and use it in GitHub Desktop.
PostgreSQL: Backup and restore pg_dump with password on command line #sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment