How to dump and restore a Postgres DB with new table ownership
Tags: mysql, postgres, backups
Published: 2024-03-14 08:20:13

---
title: How to dump and restore a Postgres DB with new table ownership
published: true
description: 
tags: MySQL, PostgreSQL, backups
cover_image: https://cdn.hashnode.com/res/hashnode/image/stock/unsplash/oyXis2kALVg/upload/fa9c7f5c689fa38c4a9edb8e48022dff.jpeg?w=1600&h=840
canonical_url: https://gemmablack.dev/how-to-dump-and-restore-postgres-db-with-new-table-ownership

# Use a ratio of 100:42 for best results.
# published_at: 2024-03-14 08:19 +0000
---

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=<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.

```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.