{"id":93337,"date":"2022-02-08T22:15:01","date_gmt":"2022-02-08T22:15:01","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93337"},"modified":"2024-04-16T18:58:58","modified_gmt":"2024-04-16T18:58:58","slug":"back-up-and-restore-postgresql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/back-up-and-restore-postgresql\/","title":{"rendered":"How to back up and restore in PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

One of the first things I worry about when I\u2019m looking at a new system is the status of the backups. I don\u2019t see anything in PostgreSQL that will lead me to a different conclusion. However, I didn\u2019t realize just how big a can of worms I would open by pursuing backups and restores.<\/p>\n

Instead of talking immediately about backups, let\u2019s talk about restores.<\/p>\n

Restore strategy<\/h2>\n

There\u2019s a saying that, as far as I know, originated with Kimberly Tripp<\/a>: Your backups are only as good as your last restore. In short, you have to take backups, but what\u2019s important is the ability to restore the data. You can have an infinite number of backups, but if you can\u2019t restore even one of them, that\u2019s nothing but wasted space. So, before you start thinking about backups, you should be thinking about how you want to restore your systems.<\/p>\n

Restoring a database is an enormous topic. This article could get into all sorts of permutations about all the different methods for restoring a database. However, to keep the size of this article to something reasonable, and because I haven\u2019t learned everything I need to just yet, I\u2019m going to focus on one restore mechanism: restore from a backup. It\u2019s also possible to restore to a point-in-time, but I\u2019m saving the details of how to do that for a future article.<\/p>\n

A simple restore from a backup is meant to get the database back to the state it was in right at the point that a backup was taken. This kind of restore operation is acceptable for development and test systems. It\u2019s an easy way to quickly migrate a database to a new server. However, it is limited in that you can only restore to the point of the backup. If data or structural changes occurred after the backup that you\u2019d like to recover, you just can\u2019t do it.<\/p>\n

This is where the point in time restore comes into play. Generally, most organizations frown on data loss. For example, taking a backup once a day means that, if a problem occurs about 12 hours into the day, you\u2019re going to lose all that data. A point-in-time recovery means you can get the last backup and all the changes to the database since that backup. Point-in-time recoveries are accomplished through the write ahead log (WAL) maintained by PostgreSQL.<\/p>\n

The WAL is automatically created and maintained by your servers. You can configure the size and number of files in the WAL. In theory, an infinite number of WAL files can be played back to arrive at a point in time. However, that brings up another aspect of a restore strategy, recovery time.<\/p>\n

A recovery strategy must be built to define how you would like to restore and how long you want it to take. Development systems usually require taking a quick database backup and you\u2019re done. Production systems need point-in-time restores, so you probably want a database backup that requires managing the WAL files.<\/p>\n

With the requirements defined, you can then walk over and pick up the right backup tool.<\/p>\n

Database back up<\/h2>\n

If simple database restores are all you\u2019re interested in, then the best tool for the job is pg_dump<\/code>. The core syntax is simple:<\/p>\n

pg_dump\u00a0databasename\u00a0>\u00a0backupfile<\/pre>\n
However, because I\u2019m running my databases, either in containers or out in the cloud, this simple syntax won\u2019t work because the service is running under a different login than what I\u2019m using to connect remotely. Instead, I can modify the command slightly:<\/p>\n
pg_dump\u00a0-U\u00a0postgres\u00a0postgrelearning\u00a0>\u00a0butest1<\/pre>\n
This command will work because I\u2019m supplying the user that runs the service.<\/p>\n
When running PostgreSQL in Docker, you must open a shell command to the container like this where PostgreHome is the name of the container:<\/p>\n
docker\u00a0container\u00a0exec\u00a0-it\u00a0PostgreHome\u00a0\/bin\/bash<\/pre>\n
Then running the original syntax just works, and the backup file will be found in the directory from where you are running the command unless you supply a path.<\/p>\n
When running the command supplying the user from the external command line pointing it to the container, it\u2019s going to demand a password. Once supplied, though, again, it just works.<\/p>\n
You can exercise more control by setting various options in pg_dump<\/code>, and I\u2019ll discuss several of these a little later. However, one option to immediately consider, especially when thinking about how you intend to restore the database, is to change the output. By default, pg_dump goes to a plain SQL file. Pg_dump<\/code> can also go into a compressed, digital file format by adding -Fc<\/code> which is the custom format:<\/p>\n
Pg_dump -U postgres -Fc postgrelearning > bucustom.dmp<\/pre>\n
Using either of these two methods, you can back up your database. Now let\u2019s talk about running a restore.<\/p>\n
Database recovery<\/h2>\n
The output from the default pg_dump<\/code> command is interesting. However, it gets very long, very quickly. I have a very simple database created from other scripts in this series so far. Here\u2019s a small part of the output from pg_dump<\/code> with the default format that you can see by using CAT<\/code>:<\/p>\n
--\r\n-- PostgreSQL database dump\r\n--\r\n-- Dumped from database version 14.1 (Debian 14.1-1.pgdg110+1)\r\n-- Dumped by pg_dump version 14.1\r\nSET statement_timeout = 0;\r\nSET lock_timeout = 0;\r\nSET idle_in_transaction_session_timeout = 0;\r\nSET client_encoding = 'UTF8';\r\nSET standard_conforming_strings = on;\r\nSELECT pg_catalog.set_config('search_path', '', false);\r\nSET check_function_bodies = false;\r\nSET xmloption = content;\r\nSET client_min_messages = warning;\r\nSET row_security = off;\r\n--\r\n-- Name: hsr; Type: SCHEMA; Schema: -; Owner: postgres\r\n--\r\nCREATE SCHEMA hsr;\r\nALTER SCHEMA hsr OWNER TO postgres;\r\nSET default_tablespace = '';\r\nSET default_table_access_method = heap;\r\n--\r\n-- Name: radiobrand; Type: TABLE; Schema: hsr; Owner: postgres\r\n--\r\nCREATE TABLE hsr.radiobrand (\r\n    radiobrandid integer NOT NULL,\r\n    radiobranddesc character varying(50) NOT NULL\r\n);<\/pre>\n
It keeps going from there. In short, pg_dump<\/code> is scripting out the database, structures, and data, into a file. It\u2019s an SQL file that you can then run from psql or any other tool. You\u2019ll get the objects and data created using the SQL from the pg_dump process. Since it does include data, this file could be extremely large, depending on the database.<\/p>\n
Recovery then is just a question of replaying the scripts, in other words, just executing the SQL. You could open it in the GUI of your choice, or you could take advantage of the command line utility like this:<\/p>\n
Psql -U postgres postgrelearning < butest1<\/pre>\n
Of course, you would have to ensure that you have an empty database, otherwise, this will attempt to rebuild and reinsert data that\u2019s already there. You can drop the existing database, create a new one and then run the scripts, or you can drop all the objects from the database, and then run the scripts.<\/p>\n
If you choose to get the custom file format, you won\u2019t have a simple set of SQL scripts, so replaying them requires a new command, pg_restore<\/a>.<\/p>\n
The base command to use pg_restore<\/code> is just as simple as pg_dump:<\/p>\n
pg_restore -d postgrelearning -U postgres bucustom.dmp<\/pre>\n
The same rules apply. The database must be either a new database or one you\u2019ve cleaned unless you want to take advantage of the additional functionality I talked about when using the custom file format. For example, you could restore to a database that still has all its objects inside. You can choose to drop the existing objects first by modifying the command as follows:<\/p>\n
pg_restore -d postgrelearning -U postgres -c bucustom.dmp<\/pre>\n
The command -c<\/code>, or --clean<\/code>, will drop objects first, however, you could run into error messages if you add a new object after the backup was taken. You can also tell pg_restore<\/code> to create the database first:<\/p>\n
pg_restore -d postgres -U postgres -C bucustom.dmp<\/pre>\n
The -C<\/code>, or --create<\/code>, command will try to create a postgrelearning database. If one exists, you\u2019ll get an error, so, you can again modify the pg_restore<\/code> command:<\/p>\n
pg_restore -d postgres -U postgres -C -c bucustom.dmp<\/pre>\n
Running both --clean<\/code> and --create<\/code> will result in pg_restore<\/code> dropping and recreating your database. Note that you will initially connect to a different database with the -d<\/code> option and that the restore will use the database name from the dump file.<\/p>\n
While running the SQL scripts of a basic pg_dump<\/code> command is simple, you don\u2019t have much control. You will find there are quite a few options if you use the custom file format and pg_restore<\/code>. Using pg_restore<\/code> you can:<\/p>\n