How to migrate PostgreSQL database

Posted on In Views: Word count in article: 1.4k Reading time ≈ 1 mins.

It has been a long time since my last deployment of PostgreSQL. Now, I need to migrate it to a new server due to storage space issue.

How to migrate

Assuming that the old server is server A, the new server is server B.

  1. Dump your database file in the old server

    log into server A as root

    1
    2
    3
    4
    5
    6
    su - postgres
    pg_dump [database_name] > /tmp/db.sql
    exit
    scp /tmp/db.sql root@serverB_domain_or_ip:/root
    rm /tmp/db.sql
    exit

  2. Install PostgreSQL and create a new database in server B

    log into server B as root

    1
    2
    3
    apt update
    apt install postgresql
    vim /etc/postgresql/14/main/pg_hba.conf
    1
    2
    3
    4
    5
    # change peer with md5 in order to make password access as local user.
    local   all             all                                     md5
    # allow remote connection for both IPV4 and IPV6
    host    your_database_name    your_username    0.0.0.0/0    scram-sha-256
    host    your_database_name    your_username    ::/0    scram-sha-256
    1
    vim /etc/postgresql/14/main/postgresql.conf
    1
    2
    # find and change this line as following for remote connection.
    listen_addresses = '*'
    1
    2
    3
    mv /root/db.sql /tmp/db.sql
    su - postgres
    psql
    1
    2
    3
    CREATE USER your_username WITH PASSWORD 'your_password';
    CREATE DATABASE your_database_name WITH OWNER = your_username;
    exit

  3. Restore with your database file

    1
    2
    psql your_database_name -U your_username < /tmp/db.sql -W
    rm /tmp/db.sql

If you only want to migrate a single table

1
2
3
4
# replace tutorial before with the following commands
pg_dump -U source_user -d source_db -t table_name > table_name.sql

psql -U target_user -d target_db -f table_name.sql

Reference

  1. https://blog.csdn.net/zhangzeyuaaa/article/details/77946976