In this article, we 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:
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.
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:
The program will prompt you to specify a name for the dump and a path to save the backup file. In the "Format" field, we 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:
As we 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.
The resulting file must be transferred to another VPS or dedicated server. The procedure is simple here:
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.
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:
Then transfer the backup to the new server in the same way and import it. Standard recovery functions will not work here, instead, we will have to execute the SQL script contained in the dump file. Do the following:
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, we 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!