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.
Top comments (1)
Thanks for documenting that. I'll know where to look should I need to do this myself...