This post was originally published in 2022, and we’ve updated it in 2025 for clarity and relevance, reflecting current practices while honoring its original perspective.
If you’re a DBA who’s comfortable with Oracle, MySQL, or SQL Server, stepping into PostgreSQL can feel both familiar and different at the same time. The fundamentals of protecting data don’t change (as backups and restores remain non-negotiable), but the way you do them in Postgres isn’t quite the same.
This guide is a practical walk-through of PostgreSQL’s native tools for logical and physical backups, plus the restore options you’ll use most often. Think of it as a quick start to handling one of the most important DBA responsibilities in your new Postgres environment.
For the purpose of this mini-tutorial, we assume all tasks will be performed by the user “postgres,” who has superuser privileges on the database unless otherwise noted.
Backups are an essential part of any database management strategy and are crucial for preventing data loss and ensuring business continuity. However, the backup approach that works for one organization may not be effective for another, as each environment is unique. Despite this, there are common reasons for data loss that everyone should be aware of, including:
By understanding the common reasons for data loss and the importance of backups, you can develop an effective PostgreSQL database backup and restore plan moving forward.
Logical backups are processed with native tools such as pg_dump and pg_dumpall. These tools should be included in the default bin directory for postgres installation, such as /usr/pgsql-11/bin. If your path is not set, you may want to include the bin directory in your path.
Many options can be used when running these tools to customize your data dumps. So, we will cover a few scenarios in this blog.
Logical backups are an essential part of any database management strategy, providing numerous benefits that are critical to maintaining the integrity of the data. Here are some of the benefits of using logical backups:
Flexibility: Logical backups provide a high degree of flexibility, allowing you to backup and restore specific tables or subsets of data rather than having to restore the entire database. This is particularly useful when dealing with large databases, where restoring the entire database can be time-consuming and resource-intensive.
Portability: Logical backups are also highly portable, meaning they can be easily transferred between different instances of PostgreSQL or even between different database management systems. This is particularly useful when migrating data from one system to another, as it allows you to transfer only the data you need rather than transferring the entire database.
Version control: Logical backups allow for version control, enabling you to track modifications to the database over time. This is advantageous for debugging problems and performing audits.
Granular control: An advantage of utilizing logical backups in PostgreSQL is having control over the backup process at a granular level. By customizing the backup format and options, complex data structures can be backed up efficiently while ensuring that specific data is included. Furthermore, logical backups can be compressed, which optimizes storage utilization.
Transparency: Logical backups can be easily inspected and modified, as they are in a human-readable format, making it easier to troubleshoot issues that may occur.
Want to learn more about logical backups? Check out the blog Replication Between PostgreSQL Versions Using Logical Replication.
Physical backups are processed with native tools such as pg_basebackup. Again, these tools should be included in the default bin directory for postgres installation, such as /usr/pgsql-11/bin. If your path is not set, you may want to include the bin directory in your path.
You can also use system tools for physical backups, such as tar or other archiving tools at your disposal.
Physical backups are a crucial component of a comprehensive database management plan, delivering numerous advantages that are essential for preserving data reliability. Below are some of the benefits of using physical backups:
Faster backup and restore times: Physical backups are faster than logical backups as they do not have to translate the data into SQL statements. They simply copy the files containing the data, resulting in a quicker backup and restore process.
Ideal for large databases: Physical backups are more efficient when dealing with large databases as they do not require as much disk space as logical backups. By taking a physical backup of a PostgreSQL database, all system tables and indexes, along with any user data, are captured, ensuring that in the event of a disaster, no data will be lost.
Lower risk of data corruption: Since physical backups operate at the file level, there is a lower risk of data corruption than logical backups, which operate at the SQL level.
Flexibility: Physical backups offer a high degree of flexibility regarding backup strategy, enabling DBAs to customize their backup approach to meet any particular requirements. This can include full, incremental, and differential backups.
Find out how to backup and restore a PostgreSQL Cluster with multiple tablespaces using pg_basebackup.
The source database server has to allow a remote connection for the user performing the task. Remember, we are assuming for our examples that the user is postgres.
host all postgres 0.0.0.0/0 md5
2. Edit your postgresql.conf file or whatever file you may be loading for runtime configs and change the parameter listen_addresses to the following:
listen_addresses = ‘*’
Once the above changes are made, reload your configuration file or restart postgres.
The above examples are pretty open. For security, you most likely will restrict the IP address in the hba.conf file to a more specific IP, Subnet.
In our example, we are allowing postgres to connect from anywhere with password authentication. Thus, the 0.0.0.0/0 and md5. You could change the 0.0.0.0/0 to the address of the other database server like 192.168.1.2/32. We also specify the user postgres with the -U option since it is the user we opened up in the pg_hba.conf file.
If the user running the commands has different credentials on source/target servers, you will need to save the password to .pgpass or set the environment variable PGPASSWORD so you are not prompted for the password whenever it is needed.
This is quite a simple task to perform if you have the correct privileges and configuration settings, along with the storage needed depending on your database size.
If you have only one instance of postgres running on your server and have minimal / default configuration for the pg_hba.conf file, and your path includes the postgres bin directory, all you need to do as user postgres is…
pg_dumpall > savedfile.sql
The above works well for small databases where you have space on the local server and just want a quick and simple dump of your database.
If you are running multiple instances on the local server and want to dump a specific instance, all you do is …
pg_dumpall -p port > savedfile.sql
Replace the port above with the port number the instance you wish to dump is running on.
Although this is pretty much the same thing as on a local server, there are a few things you need to have configured to execute this data dump remotely. Plus, your prerequisites need to be addressed.
Now from our remote client or server, we can run the following commands as long as the postgres tools are installed.
pg_dumpall -h host -p port -U postgres > savedfile.sql
Replace the host above with the address of the source DB and port with the port number it is running on.
There are other flags and options you can use. Have a look here for the usage options.
Similar to the other commands with a slight variation
pg_dump -d dname > savedfile.sql
Like in other scenarios, the above works well for small databases where you have space on the local server and just want a quick and simple dump of your database.
If you are running multiple instances on the local server and want to dump from a specific instance, all you do is …
pg_dump -p port -d dbname > savedfile.sql
Similar to the other commands with a slight variation
pg_dump -d dname -t tablename > savedfile.sql
Like in other scenarios, the above works well for small databases where you have space on the local server and just want a quick and simple dump of your database.
If you are running multiple instances on the local server and want to dump from a specific instance, all you do is …
pg_dump -p port -d dbname -t tablename > savedfile.sql
If you want more than one table, list their names or patterns like so …
pg_dump -d dname -t table1 -t table2 -t table3 > savedfile.sql
Just like in previous examples, specify the connection options with -h host -p port
This is just as simple as the above data dumps. However, keep in mind that this will not get you what you need if your instance is an RDS instance. Amazon really locks down what you can do as a privileged user on an RDS instance, even as Postgres.
pg_dumpall -g > users.sql
pg_dumpall -g -h host -p port -U postgres > users.sql
You can edit the above dump file and remove any user you do not wish to apply when you restore the file to a different server.
Restoring the newly created backup is a simple task. There are several ways to accomplish this, and we will go over a few of these just to get you going. Keep in mind there is a pg_restore utility as well, which we will not be addressing in this blog. Pg_restore lets you get more creative with your dumps and imports.
Again, we assume all actions here are executed as user postgres.
psql postgres -f savedfile.sql
psql -h host -p port postgres -f savedfile.sql
pg_dumpall | psql -h host -p port postgres
pg_dumpall -h src_host -p src_port | psql -h target_host -p target_port postgres
psql dbname -f savedfile.sql
psql -h host -p port dbname -f savedfile.sql
Sometimes you don’t have access to the users and credentials on a source database or want them to differ on your target/restored database. Follow these steps to achieve this.
pg_dump -d database –no-owner > savedfile.sql
psql -U newowner dbname -f savedfile.sql
Remember, for remote servers, as noted in the other examples, use the -h host -p port and any other connection string option needed.
If the user’s credentials are different and you are prompted for passwords, read the prerequisites section of this blog.
A common way of performing physical backups in Postgres is using pg_basebackup. This tool allows us to generate a physical backup with the necessary WAL files needed to restore or stand up a stand-alone instance.
There are many flags and options for this tool, including compression, but for the sake of this blog, we will focus on the basic use of pg_basebackup with minimal options.
For the purpose of this document, we will cover physical backups using the native pg_basebackup tool.
NOTE: Typically, one specifies the destination path for the physical backup. This is noted with the -D option of pg_basebackup.
pg_basebackup -D /destination/path -Pv –checkpoint=fast
pg_basebackup -D /destination/path -Pv –checkpoint=fast -F t
The above will generate two tar files. A base.tar and a pg_wal.tar
Make sure you have set up the prerequisites as explained here.
The only difference between remote and local execution is that for remote, we specify a source server with the -h remote_host and the port postgres is running on with the -p remote_port
pg_basebackup -h host -p port -D /destination/path -Pv –checkpoint=fast
If the user executing pg_basebackup is not trusted directly from the server executing the pg_basebackup, add the additional option of -U username. For example …
pg_basebackup -U postgres -h host -p port -D /destination/path -Pv –checkpoint=fast
If you execute the pg_baseback with the tar file option, it will generate two tar files. A base.tar and a pg_wal.tar
Extract the base.tar. If you do not have different WAL files to restore, extract the pg_wal.tar and place the wal segment file in the pg_wal directory.
Make sure the directory where the new cluster will be located exists with the proper permissions and storage capacity. Remember, this will consume the same amount of space as the source database.
Define where the target database will reside.
As user postgres, run the following command assuming pg_basebackup is in your path.
pg_basebackup -D /destination/path-Pv –checkpoint=fast -X stream
pg_basebackup -h host -p port -D /destination/path-Pv –checkpoint=fast -X stream
What does the above do?
The above applies to whether the database is remote or not.
When the pg_basebackup completes, to start up the new local instance, go into the new data directory /destination/path, modify the postgresql.conf file or whatever file you may have defined your previous port in.
You can then proceed to start the new instance of postgres as follows:
pg_ctl -D /destination/path -o “-p 5433” start
You should now be able to connect to the new cluster with the exact credentials as the source cluster with
psql -p 5433
This process is pretty much identical to the local cluster process above. The only difference is you will specify a host and credentials.
From the remote target host
pg_basebackup -h source_server -p port -U username -D /destination/path -Pv –checkpoint=fast -X stream
As you can see, we are simply adding a connection string to the original command we ran for the local copy. This will generate the backup on the remote host and save it to the local destination path.
Once the copy is placed on the target host, if necessary, change your port and archive location if archiving is enabled, as mentioned above.
Getting PostgreSQL backup and restore right is one of the first things you’ll do as a Postgres DBA. Logical backups give you portability and control, while physical backups are faster and ideal for full recoveries. Both are essential, and testing your restores regularly is the only way to be sure your data is safe.
But backups are just one piece of the bigger challenge. As your PostgreSQL environment grows, so do the demands: high availability, security, compliance, upgrades, and performance tuning all pile on. What starts as straightforward quickly becomes more work than most teams expect.
If you want a clear picture of what it really takes to manage PostgreSQL at scale — and how to avoid being caught off guard — read our guide on PostgreSQL in the Enterprise: The Real Cost of Going DIY.
