-
-
Save doArcanjo/1ad8d8e7dc625332cc4bcfba298ecca4 to your computer and use it in GitHub Desktop.
PostgreSQL: Backup and restore pg_dump with password on command line #sql
This file contains hidden or 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