Database backups are extremely important and absolutely necessary for protection against data loss. The lack of database backups can totally mess up business operations and can be devastating for a business. In this tutorial below we will teach you how to backup a PostreSQL database. PostgreSQL is an object-relational database management system used by many developers to store and manipulate data for their applications and websites.
When you are storing important data in a PostgreSQL database like information about your customers, products, sales, employees etc. you need to make sure you have a full backup of that database. Performing regular database backups very important as it will save you lot of time in case of data loss. When you install PostgreSQL on a Linux VPS, it includes tool which helps you to better manage the database service. There is also a tool for generating database backups and it is really easy to use. Follow the steps bellow to find out.
How to backup a PostgreSQL database on a Linux VPS
One of the prerequisites is to have SSH access to your server. If you do have access to it, connect to your Linux VPS via SSH. Once you are connected to the server, switch to the postgres user using the following command:
su - postgres
postgres is the superuser in PostgreSQL and you can use it to perform many different operations like adding users, creating or deleting databases, assigning roles etc. In this case you will be using the superuser to generate a backup of your PostgreSQL database.
To backup a single database you can use the pg_dump tool which is installed by default with your PostgreSQL service. You can use it no matter if you have an Ubuntu VPS or CentOS VPS. When you are using this tool to generate a backup of specific database, you should use the following syntax:
pg_dump [connection-option ...] [option ...] [dbname]
Now, to create a plaintext dump of the database, you can use the command bellow:
pg_dump database-name > database-name.sql
If you are not sure about the name of the database you are using for your application, you can easily check this with the psql tool. This tool is also included with the PostgreSQL service during installation. Therefore, while logged in as postgres user, type psql in your terminal to activate the PostgreSQL interactive terminal:
postgres@host:~$ psql psql (9.5.13) Type "help" for help. postgres=#
Here, type \l to list all of your PostgreSQL databases. The output will be similar to the one below, except there will be more databases:
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)
Now, lets say you want to backup the testdb. The command you need to use is the following:
pg_dump testdb > testdb.sql
How to backup all your PostgreSQL databases on a Linux VPS
The pg_dumpall tool that also comes with PostgreSQL allows you to backup multiple databases at once. You can use it to create a full backup of all your PostgreSQL database. To do that, run the command below in your terminal:
pg_dumpall > alldbs.sql
This will create a plaintext dump of all your databases currently stored in PostgreSQL so you can restore them later if needed.
How to restore PostgreSQL database on a Linux VPS
The psql tool also allows you to easily restore a database from backup if needed. To restore a database from backup, you can simply run the following command in your terminal:
psql database-name < database-name.sql
Of course, you need to replace database-name in the previous command with the name of your database. The restore operation will take some time depending on the size of the backup.
You can also restore all database from a previous backup using the following command:
psql -f alldbs.sql postgres
Make sure you replace alldbs.sql with the name of the database backup file.
For more information and usage examples about these PostgreSQL tools we recommend you to check their respective documentation pages. The documentation for pg_dump is available here, for psql here, and for pg_dumpall it is available here.
And that’s it, you have successfully learned to back and restore PostreSQL databases. We strongly suggest you do a regular daily backups of your databases and keep them in a safe place.
You really don’t need to back up or restore PostreSQL database if you use one of our premium server management services, in which case our system administrators will take care of your database backups, professionally and efficiently.