Backups are important & they are our only contingency for when we delete things that we should not or there are malfunctions hardware or otherwise. Backups at regular intervals are essential for any organization to maintain a proper working environment. Same goes for database, database backups are extremely essential as it holds transactional, user data among other things.
In this tutorial, we will learn about PostgreSQL backup. Before we start this, you can also refer to our tutorials on installing Postgresql on CentOS/RHEL, also for installation on Ubuntu & installing Postgresql from the source.
Recommended Read: Important postgresql commands for beginners
Backup Postgres Database
Postgresql provides backup utilities when we install postgresql on the server, they are pg_dump & pg_dumpall. Pg_dump is most useful for taking postgresql backup of a single database, while pg_dumpall provides the postgresql backup for all databases on the server. Let’s see some examples.
Backup Postgres database, single DB
Start by switching to the user postgres,
# su – postgres
Now to back up a database, use the following command,
$ pg_dump -U user_name -d thelinuxgurus > thelinuxgurus.bak
Here, ‘thelinuxgurus’ is the name of the database we need to backup. We can also use .sql or .tar as extension to backup file.
Taking backup of one or more tables in postgres
Command to take the backup of a single table from a database is,
$ pg_dump -U User_name -d database_name -t public.users_table > users_table.bak
To take the backup of more tables,
$ pg_dump -U User_name -d database_name -t public.users_table public.test_table public.test_table_2 > 3_tables.bak
Restoring a database
Now if we need to restore the backup, we need to drop the old database & create an empty database,
$ dropdb thelinuxgurus
$ createdb new_db
Not to restore the backup, use the following command,
$ psql new_db < thelinuxgurus.bak
Or we can also use pg_restore utility to restore a database, use the following command to restore the database using pg_restore utility,
$ pg_restore -d db_name /path/to/your/file/thelinuxgurus.bak -c -U db_user
Restore one or more tables from backup
Similarly, we can also restore the backup of one or more tables,
$ pg_restore -U user_name –data-only -d target-db-name -t table_name /path/to/your/file/users_table.bak
Taking only schema backup
To take the backup of schema only, use the following command,
$ pg_dump –schema=schema_name db_name > schema.bak
Restoring schema backup
To restore schema backup, use the following command,
$ psql -d db_name -U user_name < schema.bak
Taking all database backup
The second backup utility i.e. pg_dumpall is used for taking backup of all the databases on postgreSQL. To take backup use the following command,
$ pg_dumpall > all_databases.bak
Restore all databases backup
To restore all database backup, use the following command,
$ psql -f all_databases.bak postgres
We can also use all or any of the above-mentioned commands & create a cron job for it to automate our backups. Please refer to our tutorial on Scheduling Cron jobs on Linux system. We now end this tutorial on backup Postgres database. Please feel free to send in any questions or queries using the comment box below.
