How to transfer a PostgreSQL database to another server using pgAdmin 4

In this article, I will tell you how to properly migrate a PostgreSQL database from one server to another. Let's have a look at several options for creating a dump and further importing it to a new?VPS?or dedicated server.

The need for such a transfer may arise after an IT specialist completes the development of a PostgreSQL database through the free pgAdmin 4 program. This tool is preferred by many developers, as it is ideal for solving a variety of tasks:

• writing SQL queries;
• development of procedures;
• administration of the PostgreSQL database.

The beauty of pgAdmin 4 edition is that it works in web application mode, so you can work with the program remotely from literally anywhere in the world. This software is compatible with all current versions of?Linux,?Windows, and macOS operating systems, which only emphasizes its versatility. Let's not stray?from the point and proceed to the process of transferring the PostgreSQL database to another server.

Creating a PostgreSQL database backup in pgAdmin 4

Our task is to create a copy of the existing database and then transfer it to a new VPS server and restore the data there. All this is easily done through the client computer in the pgAdmin 4 web application:

1. Select the target database in the browser.
2. Click on it with the right mouse button and select "Backup".

The program will prompt you to specify a name for the dump and a path to save?the backup file. In the "Format" field, I leave "Custom". This format involves compression, and it is recommended for reserving large and medium-sized databases, so it will be easier to carry out the transfer. In addition to it, there are three other formats:

1. Tar (tar) – in this case, the database is not compressed.
2. Simple (plain). The output is a text SQL script containing instructions. This format is good because it allows you to edit the database dump on the go through any convenient text editor. If, after creating the dump, you plan to change something in it before importing it to a new server, then this option is optimal.
3. Directory (directory). A directory is created where all tables and volume objects are backed up as separate files. Directory format uses compression algorithms and allows you to upload data in several streams in parallel – convenient for large databases.

As I noted above, in most cases, you should leave the default "custom" format. As a result of compression, you will get a file in the extension .backup, and the system will display a successful completion message.

Importing a PostgreSQL database dump to pgAdmin 4

The resulting file must be transferred to another VPS or dedicated server. The procedure is simple here:

1. On the new server, go to pgAdmin and create an empty database. To do this, right-click on the "Databases" tab and select "Create".
2. Now right-click on the created database in the same "Databases" tab and select "Restore".
3. Next, specify the format of the database dump and the path to the file with the extension .backup, which I created a few minutes earlier.

After confirmation, importing the PostgreSQL database dump into pgAdmin 4 will begin, it can last from a split second to several minutes, it all depends on the performance of the server hardware and the file size.

Exporting and importing a database in a simple SQL format

The pgAdmin 4 graphical shell also allows you to export a?database?in the form of system SQL instructions. You need to do almost everything the same, only when choosing a format, specify "Simple", and additionally activate a couple of options in the "Upload Parameters" tab:

• Use INSERT commands.
• INSERT specifying columns.

Then transfer the backup to the new server in the same way and import it. Standard recovery functions will not work here, instead, I will have to execute the SQL script contained in the dump file. Do the following:

1. Through the context menu of the target database, go to the Query Tool.
2. Click on the "Open file" item, and in the appearing window, select the database dump in a simple SQL format created earlier.
3. Click "Execute".

If everything is done correctly, the recovery process will take a couple of moments, and you will be able to start further work. Importing a database dump in SQL format is suitable in situations when you need to transfer a database from one OS to another - for example, from Windows to Linux, from macOS to?Debian, etc.

Finally, I should add that if it is necessary to transfer a large database, the size of which is several tens or even hundreds of gigabytes, it is more reasonable to use the pg_dump or pg_dumpall console utilities, bypassing the pgAdmin 4 GUI. That's all for now, thank you for your attention!