A few days ago, I launched a website called “Vulpine Citrus”, meant to act as my front end with the rest of the world. Before that, I used to run a lot of my personal services on a server shared with a friend. After I bought my domain name, and VPS, I decided to begin moving my services onto my new server and stop hogging the storage space on that other, shared system.

I had, until then, gracefully escaped anything vaguely technically about managing database systems. I still am far from understanding the deep, underlying concepts of DB managers. However, I had an entire computer science semester of “Databases 101” last year, and I felt a little more enclined to start and play around with the PostgreSQL database system.

I will show you how to dump and export databases from one cluster to another on different machines. Both systems will be running Debian 9 or Debian 10. As far as my understanding goes, and that is all you need to understand this article, a cluster is a single instance of postgreSQL running on a system, with multiple clusters able to run at the same time, using different versions of the software.

On the former host of my database, I was running PostgreSQL 9.6. My cluster was called “9.6 main”. On the new system, I am running PostgreSQL 11. My cluster is called “11 main”. I will only be exporting one database, and creating new users. If you wish to dump and export a whole cluster, you will have to find someone more competent.

First off, logged into my new system, I had to install, enable and start postgresql 11

apt install postgresql
systemctl enable postgresql
systemctl start postgresql

You can check whether or not the server is running by using

pg_lsclusters

Which should show something similar to

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Now, head off to the old system. Log in as the owner of the database you wish to export (or any account capable of reading the database, really).

You can export the old database by using pg_dump.

pg_dump -U owner_of_database name_of_database > my_database.pgsql

What you're doing, essentially, is generating a PostgreSQL script capable of creating a carbon copy of your database as seen by user owner_of_database.

You can copy that script by any mean to the new system. I personally use scp over ssh.

Back to the new system, you will have to create your (empty) database and the role associated to the user(s) that will access it. The tools called createuser and createdb do the job fantastically, such that you don't need to write a single line of SQL.

createuser --interactive --pwprompt

You can tell PostgreSQL that your new user can create databases, or not, roles, or not, it depends on what kind of user you want. Mine is a simple account meant to just read and write the database without doing anything complex or risky with its structure, or any other database.

The proper database can be created using

createdb my_owner_name -O my_owner_name
createdb my_database_name -O my_owner_name

The new user will most probably require a database bear their name. When you log into the server without providing a database, this is where you end up. It does not fulfill any other purpose, but I like to create it as well.

Once everything is in place, and you can successfully log in using psql -U my_owner_name (or if you can't because there are no databases), create the carbon copy of your former database by executing the dumped script

psql -U my_owner_name my_database_name < dump.pgsql

If everything goes smoothly, you should only see a lot of PostgreSQL instructions fly by in the command line. When the database is created, you can try and log into it. Check whether you can successfully log in

psql -U my_owner_name my_database_name

Once you are sure that everything was transferred as you wanted, you can go back to your previous system, and, optionally, clean up the database(s) you exported.

dropdb my_database_name

And you'll be good to go! 🦊