Summary
in this tutorial, I will show you how to backup and restore databases using PostgreSQL backup tools.
Backup
pg_dump
To backup one database, you can use the pg_dump tool
pg_dump does not block other users accessing the database (readers or writers).
| Parâmetro | Parametro | Descrição |
|---|---|---|
| -h | --host | database server host |
| -U | -–username | database user |
| -–schema | Informar o schema que gerará o backup | |
| -W | --password | force password prompt |
| -v | verbose mode | |
| -p | --port | database server port number |
pg_dump database_name --host=host_name --username=user --schema=public -W -O -v > backup_db_$(date +%d_%m_%Y).sqlpg_dumpall
pg_dumpall is a utility for writing out ("dumping") all PostgreSQL databases of a cluster into one script file.
pg_dumpall also dumps global objects that are common to all databases (pg_dump does not save these objects.)
This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.
| Parâmetro | Parametro | Descrição |
|---|---|---|
| -h | --host | database server host |
| -U | -–username | database user |
| -–schema | Informar o schema que gerará o backup | |
| -W | --password | force password prompt |
| -v | verbose mode | |
| -p | --port | database server port number |
| -a | --data-only | dump only the data, not the schema |
| -O | --no-owner | skip restoration of object ownership |
| -r | --roles-only | dump only roles, no databases or tablespaces |
| -s | --schema-only | dump only the schema, no data |
| -t | --tablespaces-only | dump only tablespaces, no databases or roles |
| -x | --no-privileges | do not dump privileges (grant/revoke) |
pg_dumpall -p <port> -h <host> -U <user> -W > <path/filename.sql>Ex:
pg_dumpall -p 5432 -h localhost -U postgres -W > /backup/backup_completo_postgres.sqlOBS: If you are not specifying fully qualified paths, like:
pg_dump your_db_name > dbdump
then the file will be stored:
- in current user's home directory (Windows0
- in current directory (Linux)
Backup Roles Only
This is helpful in the test phase, which you do not want keep the old test data populated during the testing period.
pg_dumpall --roles-only --host=host_name --username=postgres -W > roles.sqlRestore
psql --dbname=bd_name --host=host_name --username=user -W < backup_db_file.sql