Hello! Migrating a database between servers is a very common question. In this article we will talk about postgres migrate database to new server topic. Answer of this question depends on the size, load and critically of the database.

Database migration is a very important topic. There are a lot of parameters effecting migration method, downtime and migration performance. Please consider consulting to an expert if you don’t have experience about migrations.

Why to Migrate a New Server?

Usually preferred method is upgrade is not a migration. (For all database engines) but in some cases like datacenter change, new hardware, virtualization changes may required Postgres migrate database to new server.

Any kind of migration, not only database servers, is a very painful process. Grants, objects, data, scheduled jobs, OS tuning, environmental variables etc.

While managing an environment please keep in mind “Knowledge is nothing, documentation is everything.” Everything you do, please write it to a documentation. Every command you run, with expected output, with a clear explanation and purpose.

PostgreSQL Migration Strategies

There are several strategies you can use for Postgres migration. But in this article, I will cover migration strategies using pg_dump.

When it comes to migrating your database, choosing the right strategy is crucial for a smooth and successful transition. One widely used approach is leveraging pg_dump, a powerful tool that allows you to extract a PostgreSQL database into a script file and then restore it. This method is particularly popular due to its simplicity and reliability.

The pg_dump utility provides various options to customize the database dump according to your specific requirements. For instance, you can choose to export only a subset of the database objects, such as tables or schemas, or include specific data only. Additionally, pg_dump supports parallel dumps, which can significantly accelerate the migration process for large databases.

By delving into the details of using pg_dump for migration, you’ll gain insights into the various scenarios where this strategy excels, as well as considerations for optimizing the process. Whether you’re migrating to a new server, upgrading your PostgreSQL version, or moving to a cloud environment, understanding the nuances of pg_dump-based migration can be immensely beneficial.

pg_dump is a command which dump all objects in PostgreSQL database. There is another command pg_dumpall which is used for cluster databases, if you are running a single database you need to use pg_dump.

I want to take postgres migrate database to new server step by step. Each step listed here is critical. Please be careful or consult to a migration expert.

Step 1. Analyze

This is the most important step. In this step we discover the environment, OS, parameters, object, extension etc. Before starting this step start a new LibreOffice Write a document (MS Word will work too) and try to keep it as structured as you can keep.

Operating System and Level

Operating system level is the first information you need.

• Is it still supported?
• Will you use same OS?
• Will you use same OS version?
• If you are migrating to new hardware is OS same? (endiannes)

PostgreSQL Version

You need to check postgresql version, you can use my article about checking PostgreSQL version for help.

• Will you use same version?
• Is that version still supported?
• Did you check version lifecycle?
• If you are migrating to a new hardware is there any new tuning parameters?

Storage

Storage is another very important parameter for migration analysis. Different storage units have different performances, network storage is slower etc. Also you need to check mount points you will use.

OS Parameters

This is very huge topic. Checking everything is not easy but we need to be careful about this.

• Check ulimit /etc/limits.conf and migrate same values
• Check environmental variables of root
• Check environmental variables of postgres
• Check shmmax
• Check shmall

Data Change Ratio

Monitoring amount of data change (insert & updates and deletes) is important, because that will show you if you can manage the downtime required.

Step 2. Install New Environment

As next step, install the new environment. If you are not planning any change, install PostgreSQL using same paths.

Step 3. Test Test Test

That step is the next step. But i would like to highlight it here. You need to do tests, multiple tests and test many scenarios as much as you can.

Testing is the most important step. Even much more than migration itself. Plan multiple test scenarios, try to test daily usage scenarios.

I want to mention that separately, please run a performance test. Ask the monitoring and development team about the highest load of every day, every week and every month. Some databases also have yearly pick points. Plan your performance test using this input.

Step 4. Plan the Downtime

Each database migration requires a downtime. This downtime should be planned with business owners. According to critically of your database, business teams will bargain with you about this downtime. But stick to your test scenarios and do not get les than TEST TIMES x 1.2 or TEST TIMES x 1.5.

Multiplying the time tests took with 1.2 and 1.5 depends on the amount of tests, how long did you have to analyze the environment etc. I usually suggest to multiply it with something between 1.2 and 1.5 but you can chose higher or lower values according to your knowledge level about that environment and amount of tests you run.

Step 5. Migration

The original quesiton was that How Postgres Migrate database to new server? That is the moment of finalizing your effords.

First of all, export the data:

pg_dump -C database_name > data.sql

The command above outputs the data:

Move the data between serves (assuming you are running linux)

ssh data.sql postgres@ip_of_target_server:/home/postges/data.sql

On the target server:

psql < /home/postges/data.sql

These steps will take longer time according to amount of data you store.

Other Migration Options

According to business requirements you might need a very short downtime migration strategy. You may need to use a CDC tool. That requires more time and costs more to license an enterprise CDC tool.

Or even it requires much effort you can copy data files and configuration files to the new server.

Summary

In this article we talked about how to migrate a Postgresql database to a new server.

I’m here to encourage you to get involved and express your thoughts! If you’re feeling curious about the PostgreSQL database migration process, or if you’d like to share your own insights and experiences, please feel absolutely free to ask questions or leave your comments below. Your feedback and inquiries mean a lot as they can really enrich everyone’s understanding of this intricate topic. Your participation is incredibly valued, and I’m genuinely looking forward to hearing from you.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch