How to Migrate a PostgreSQL Database to a New Version on a NAS Device

Product version: 11.0

Last modified: 30 September 2024

• To migrate your PostgreSQL database to its newer version without saving the previous database version, refer to Migrating PostgreSQL Database to Its Newer Version Using Export/Import System Configuration

• To migrate your PostgreSQL database data to its newer version with keeping the older version, refer to Migrating PostgreSQL Database to Its Newer Version With Keeping the Previous Database Version.

  This option is used when it is essential not to lose database data shared by multiple users.

The steps below were tested for ASUSTOR NAS but can also be applied to other NASes where Postgres is a Docker container.

Important

Make sure to use the correct paths when migrating on a NAS other than ASUSTOR.

Migrating PostgreSQL Database to Its Newer Version Using Export/Import System Configuration

Note

The steps below were tested for migrating PostgreSQL v13 to PostgreSQL v16 but can be applied to any PostgreSQL version you need to migrate from/to.

To migrate your PostgreSQL database to its newer version without saving the previous database version on a NAS device, take these steps:

1. In your NAKIVO Backup & Replication instance, go to Settings > System Settings > Configuration.

2. Click Export System Configuration. For more details, refer to Exporting System Configuration.

3. Log into your NAS device.
   

4. Remove PostgreSQL v13 from the device.

5. Install PostgreSQL v16 on this NAS. Make sure you use the same default port: 5432.

6. Return to your NAKIVO Backup & Replication instance and reinstall it.

7. Go back to the NAS device with the clean NAKIVO Backup & Replication.

8. Open Settings > System Settings > Configuration.

9. Click Import System Configuration. For more details, refer to Importing System Configuration.

10. Your NAKIVO Backup & Replication configuration is imported with the existing data.

Migrating PostgreSQL Database to Its Newer Version With Keeping the Previous Database Version

If you want to migrate your PostgreSQL database to its newer version with keeping the older version on your NAS device, use one of the following options:

• Using SSH Console Only

• Using SSH Console and pgAdmin

Example 1: Using SSH Console Only

Note

The steps below were tested for migrating PostgreSQL v13 to PostgreSQL v16 but can be applied to any PostgreSQL version you need to migrate from/to.

The following preconditions apply:

• You have Docker installed.

• PostgreSQL v13 is installed and running on the default port, 5432.

• Nakivo is migrated to PostgreSQL v13 database.

   

1. Connect to your NAS device via SSH (you can use the PuTTY ssh client) as a root user.

2. Create a new Docker container and deploy PostgreSQL v16 inside it.

   Run the following commands:

   • To install the latest version of PostgreSQL:

   #docker pull postgres

   • To specify the version, for example, docker pull postgres:16.2:

   #docker run --name <container name> -p <port>:<port> -e POSTGRES_USER=<postgres user> -e POSTGRES_PASSWORD=<postgres user password> -e POSTGRES_DB=<database name> -d postgres

   Example:

   #docker run --name Postgres-16 -p 5435:5435 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=admin -e POSTGRES_DB=postgres16db -d postgres

   Note

   Make sure the port used by PostgreSQL v16 differs from the port used by PostgreSQL v13, for example 5435.

   A new containerID will be displayed after the last command is run.

   Example:

   add52c94d9287e7be5782e1b6f032cbeb75505ba24549badfdfd89f7532f3e23

3. Go to cd /volume1/.@plugins/AppCentral/docker-ce/docker_lib/containers/<containerID>.

4. Go to your NAS device and stop the Docker service.

5. Return to the console and run the following command:

   #vi config.v2.json

6. ExposedPorts: {"5432/tcp":{},"<port>/tcp":{}} -> leave only {"<port>/tcp":{}}.

   Example:

   

   Important

   Make sure the correct port is displayed in the hostconfig.json file:

   PortBindings :{"<port>/tcp":[

   {"HostIp":"","HostPort":"<port>"}]}

7. Start the Docker service on the NAS device.

8. To check if the port has been changed for PostgreSQL v16, run the following command:

   #docker ps

9. Run these commands to log in PostgreSQL v16 Docker:

   #docker exec -it Postgres-16 bash

   #cd /var/lib/postgresql/data

10. To make sure the port<port> is specified, run#vi postgresql.conf , set up this port and uncomment the row.

    Note

    Installing the vim utility is required.

    Example:

    

11. Restart the Docker service (if you changed the port).

12. To make sure the PostgreSQL process is running on the port <port>, run the following code:

    #cat postmaster.pid

    Example:

    

13. Log out from the Docker.

14. Run the following commands to dump the database from PostgreSQL v13 and restore it to PostgreSQL v16:

    #docker exec -it postgres-13 bash -c "PGPASSWORD=admin pg_dump --username postgres <old database name>" > /home/dbexport.pgsql

    #docker exec -i Postgres-16 bash -c "PGPASSWORD=admin psql --username postgres <database name> -p <port>" < /home/dbexport.pgsql

    Notes

    • Postgres-13 and postgres-16 stand for container names.

    • The path /home/dbexport.pgsql can be customized.

15. Go to the VM with NAKIVO Backup & Replication installed.

16. Stop the NAKIVO Backup & Replication Director service.

17. Edit theconfig.properties file.

    (for Windows OS: NBR directory > userdata > config.properties file

    for Linux OS: /opt/nakivo/director/userdata/config.properties file)

18. Enter a new port for the PostgreSQL v16 server.

19. Type in a new database name.

    Example for Windows OS:

    

20. Save the changes.

21. Restart the NAKIVO Backup & Replication Director service.

The following postconditions apply:

The database is removed from PostgreSQL v13.

Run the following commands:

• To go to PostgreSQL v13 database location in Docker:

  #docker stop postgres-13 (drops current connections to the database (if you receive the error when running))

  #docker start postgres-13/

  #docker exec -it postgres-13 bash

  #cd /usr/lib/postgresql/13/bin

  #psql -p 5432 --username postgres

  Note

  Postgres-13 stands for container name.

• To see all existing databases:

  <postgres=#> \l

• To remove the database from PostgreSQL v13:

  <postgres=#> DROP DATABASE <old database name> WITH (FORCE);

  Note

  Using WITH (FORCE) is optional.

• To check the list of all existing databases in PostgreSQL v13 and make sure the old database was successfully removed:

  <postgres=#> \l

Example 2: Using SSH Console and pgAdmin

Note

The steps below were tested for migrating PostgreSQL v13 to PostgreSQL v16 but can be applied to any PostgreSQL version you need to migrate from/to.

The following preconditions apply:

• You have Docker installed.

• You have pgAdmin installed and running on the default port, 5430 ( (https://ip_address_of_pgadmin:5430/login?next=/).

• PostgreSQL v13 is installed and running on the default port, 5432.

• Nakivo is migrated to PostgreSQL v13 database.

   

1. Log into pgAdmin (use the following credentials: user@domain.com / password: pgadmin)

2. Register the PostgreSQL v13 server in pgAdmin (user - postgres, pass - admin, port (default) - 5432)

   

   

   

3. Select the existing database in PostgreSQL v13.

4. Right-click the database and select the Backup option.

   

5. Type in the export file name and select the destination.

   

6. Export the database from PostgreSQL v13.

7. Connect to your NAS device via SSH (you can use the PuTTY ssh client) as a root user.

8. Create a new Docker container and deploy PostgreSQL v16 inside it.

   Run the following commands:

   • To install the latest version of PostgreSQL:

   #docker pull postgres

   • To specify the version, for example, docker pull postgres:16.2:

   #docker run --name <container name> -p <port>:<port> -e POSTGRES_USER=<postgres user> -e POSTGRES_PASSWORD=<postgres user password> -e POSTGRES_DB=<database name> -d postgres

   Example:

   #docker run --name Postgres-16 -p 5435:5435 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=admin -e POSTGRES_DB=postgres16db -d postgres

   Note

   Make sure the port used by PostgreSQL v16 differs from the port used by PostgreSQL v13, for example 5435.

   A new containerID will be displayed after the last command is run.

   Example:

   add52c94d9287e7be5782e1b6f032cbeb75505ba24549badfdfd89f7532f3e23

9. Go to cd /volume1/.@plugins/AppCentral/docker-ce/docker_lib/containers/<containerID>.

10. Go to your NAS device and stop the Docker service.

11. Return to the console and run the following command:

    #vi config.v2.json

12. ExposedPorts: {"5432/tcp":{},"<port>/tcp":{}} -> leave only {"<port>/tcp":{}}.

    Example:

    

    Important

    Make sure the correct port is displayed in the hostconfig.json file:

    PortBindings :{"<port>/tcp":[

    {"HostIp":"","HostPort":"<port>"}]}

13. Start the Docker service on the NAS device.

14. To check if the port has been changed for PostgreSQL v16, run the following command:

    #docker ps

15. Run these commands to log in PostgreSQL v16 Docker:

    #docker exec -it Postgres-16 bash

    #cd /var/lib/postgresql/data

16. To make sure the port<port> is specified, run#vi postgresql.conf , set up this port and uncomment the row.

    Note

    Installing the vim utility is required.

    Example:

    

17. Restart the Docker service (if you changed the port).

18. To make sure the PostgreSQL process is running on the port <port>, run the following code:

    #cat postmaster.pid

    Example:

    

19. Return to pgAdmin.

20. Register the PostgreSQL v16 server in pgAdmin (user - postgres, pass - admin, port - custom port <port>).

21. Select the <database name>.

22. Right-click on it and select the Restore option.

    

23. Select the database exported from PostgreSQL v13.

    

24. Complete the database import.

25. Go to the VM with NAKIVO Backup & Replication installed.

26. Stop the NAKIVO Backup & Replication Director service.

27. Edit the config.properties file.

    (for Windows OS: NBR directory > userdata > config.properties file

    for Linux OS: /opt/nakivo/director/userdata/config.properties file).

28. Enter a new port for the PostgreSQL v16 server.

29. Type in a new database name.

    Example for Windows OS:

    

30. Save the changes

31. Restart the NAKIVO Backup & Replication Director service.

The following postconditions apply:

To remove the database from PostgreSQL v13, go back to pgAdmin, select the database, and click Delete (Force) to delete it.