# How to dump and restore a Postgres DB with new table ownership I've used [MySQL](https://www.mysql.com/) for years. But recently, I found myself working with [PostgreSQL](https://www.postgresql.org/) and simple things like dumping and restoring a database are different enough that I decided to document the process. It's straightforward enough once I knew how. So first, I'll do a quick overview and then share the explanations afterwards. ## Quick Overview There are several ways to dump and restore data in Postgres if you have more specialist needs. I've chosen one almost analogous to how I would do it with MySQL. ### Requirements You need the correct privileges to both [dump](https://www.postgresql.org/docs/current/backup-dump.html) the database and recreate all the database objects. ### Set the user credentials Instead of passing options to `pg_dump` and `psql`, we can also set up our access credentials as environment variables. There are more [secure alternatives](https://www.postgresql.org/docs/current/libpq-pgpass.html) to handling database access secrets though. ```bash export PGHOST= export PGUSER= export PGPASSWORD= ``` ### Dumping the data Notice how we dump the data with the `--no-privileges` and `--no-owner`. Don't include this if you want to keep the ownership as is. ```bash pg_dump \ --no-privileges \ --no-owner \ dbname > dbname.sql ``` ### Creating the new database Create a new database from a template. ```bash psql \ -c "CREATE DATABASE newdb TEMPLATE template0;" ``` ### Restoring the data Notice I used `--single-transaction` to import the data. If you don't mind importing the data where there are some errors, remove that option. ```bash psql \ --single-transaction \ new_db < dbname.sql ``` ### Changing table ownership Finally, when working with MySQL, I don't have to worry about table ownership. I just use grants and privileges. But when you import data in Postgres, it assumes the owner is the user associated with the connection. One way I've seen how to change table ownership to one we want is to generate the `ALTER` commands and then run that. ```bash psql newdb ``` Then within the `psql` shell, we create the `ALTER` commands. ```bash ## :psql >> select 'ALTER TABLE ' || t.tablename || ' OWNER TO new_owner;' from pg_tables t where t.tableowner != 'rdsadmin'; ``` It will print out something like the following, which you will need to copy-paste, and run within the shell again. ```bash ## :psql >> ALTER TABLE groups OWNER TO new_owner; ALTER TABLE images OWNER TO new_owner; ALTER TABLE jobs OWNER TO new_owner; ``` And that's it. * We've dumped the database. * We've restored it. * And we've changed the table ownership to what we want. Now, I'll share a few things I found in the documentation. ## Notes on how the dump and restore work ### Libpq Libpq is the "C application programmer's interface to PostgreSQL". So I needed to install [Libpq](https://www.postgresql.org/docs/9.5/libpq.html) before being able to run some commands on my local environment. For me on MacOS, it was not installed with Postgres and had to be done separately. ### Table locks With MySQL, dumping the database with table locks means users may experience slow responses or even downtime. To prevent table locks in MySQL, we can do the following: ```bash mysqldump \ --single-transaction \ --skip-lock-tables \ dbname > dbname.sql ``` The `--single-transaction` tells the `mysqldump` to "put everything into a transaction", and "read the database in the current state and create a consistent data dump". But it will still lock the tables unless we provide `--skip-lock-tables`. That way, we get a consistent data output and no table locks. > ↗️ Run mysqldump without locking the tables - [https://mysqldump.guru/run-mysqldump-without-locking-the-tables.html](https://mysqldump.guru/run-mysqldump-without-locking-the-tables.html) In Postgres, this is not a problem as "dumps created by pg\_dump are internally consistent, that is, updates to the database while pg\_dump is running will not be in the dump. pg\_dump does not block other operations on the database while it is working." Therefore, no extra options are needed. Of course, there are exceptions. > ↗️ Chapter 9. Backup and Restore - [https://www.postgresql.org/docs/7.2/backup.htm](https://www.postgresql.org/docs/7.2/backup.htm) ### Using options instead of environment variables for connection settings Similar to MySQL, we have `-h host`, `-p port` , but we have `-W password` for the prompt and a capital `U` for `-U user`. ### Removing ownership If you want the table ownership and privileges to remain the same, providing `--no-privileges` and `--no-owner` is not necessary. However, without this, you get the following SQL in your SQL dump: ```bash CREATE SCHEMA abc; ALTER SCHEMA abc OWNER TO existing_user; ``` ### Database Templates [Database Templates](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html) in Postgres is a foreign concept to me in MySQL. However, when we do, `CREATE DATABASE` in Postgres, it's an alias for `CREATE DATABASE dbname TEMPLATE template1`. Now, there are two templates by default. `template0` and `template1`. In the earlier example, I used `template0`, but why? * `template1` can be amended so you can create a new database with your customisations. * `template0` doesn't have any customisations. So it's a pure, unadulterated database that you can clone and copy. ### All-or-nothing imports Something interesting we can do in Postgres is ensure our imported data is consistent. If there are any errors when importing the dumped file, it will roll back all changes. To do this, we use `--single-transaction` on the import: ```bash psql \ --single-transaction \ < dumpfile.sql ``` > Note: "When using this mode, be aware that even a minor error can rollback a restore that has already run for many hours. However, that might still be preferable to manually cleaning up a complex database after a partially restored dump." - [postgresql.org](https://www.postgresql.org/docs/current/backup-dump.html#:~:text=When%20using%20this%20mode%2C%20be,after%20a%20partially%20restored%20dump.) ## That wasn't so bad MySQL and Postgres are similar but they're **not** the same. Hopefully dumping and restoring in Postgres will be simpler (even if it's just for me) in future.