How to dump and restore a Postgres DB with new table ownership

I've used MySQL for years. But recently, I found myself working with PostgreSQL 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 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 to handling database access secrets though.

export PGHOST=<host>
export PGUSER=<user>
export PGPASSWORD=<password>

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.

pg_dump \                                                                          
  --no-privileges \
  --no-owner \ 
  dbname > dbname.sql

Creating the new database

Create a new database from a template.

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.

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.

psql newdb

Then within the psql shell, we create the ALTER commands.

## :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.

## :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 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:

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

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

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:

CREATE SCHEMA abc;

ALTER SCHEMA abc OWNER TO existing_user;

Database Templates

Database Templates 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:

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

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.

Comments

[Philip Perry]

Thanks for documenting that. I'll know where to look should I need to do this myself...