PostgreSQL Backup Database and restore
How to backup and restore Database
* Postgres dump all the Database
- pg_dumpall
- pg_dump
pg_dumpall dumps all databases in given PostgreSQL installation. Everything goes there. Additionally, it dumps global things – roles and table spaces, which cannot be dumped by pg_dump.
The major benefit of pg_dumpall is that it’s single command, and you get results.
Run the bellow command 1by 1
- sudo su
- su postgres
- pg_dumpall -U postgres > “/opt/backups/alldb.pgsql”
- You can add you own path don’t forgot to add 777 permission to backup folder chmod -R 777 backups
restore DB - sudo su
- su postgres
- cd /opt/backups
- psql -U postgres < ‘alldb.pgsql’
* Postgres dump selected Database and restore
- pg_dump -U user_name databse_name > “/opt/backups/databse_name.pgsql”
- ex: pg_dump -U postgres testDB > “/opt/backups/testDB.pgsql”
restore DB - psql -U user_name databse_name < “/opt/backups/backups/testDB.pgsql”
- Ex: psql -U postgres testDB < “/opt/backups/backups/testDB.pgsql”