Migrating a stand-alone PostgreSQL instance to a cluster node

Edit online

Use these steps to convert a PostgreSQL instance to a Patroni cluster, and then add other nodes to the cluster.

About this task

You can use one of the following scenarios to migrate your existing applications to PostgreSQL HA that have PostgreSQL as a backend.
  • Use current application server that has PostgreSQL daemon that is running as a node in PostgreSQL to Patroni cluster.

    Make sure that data directory path and folder name is same as mentioned in the patroni_config.yaml file. If it is not the same, make sure to do necessary changes in patroni_config.yaml on all nodes.

  • Migrate data from current application server to Patroni cluster node.

    Back up your data with backup and restore PostgreSQL utility or copy the entire PostgreSQL data folder to the node. Check that the data folder has appropriate ownership and permissions.

Follow these common steps:

Procedure

  1. Start PostgreSQL daemon: 
    <BIN_DIR>/pg_ctl -D <DATA_DIR> start
    Where,
    • <BIN_DIR>

      PostgreSQL bin directory

    • <DATA_DIR>

      PostgreSQL data directory

  2. Connect to the PostgreSQL instance that uses psql client: 
    
cd  to <BIN_DIR>
./psql -U <USERNAME> -d <DATABASE_NAME> -h <HOSTNAME>
    For example, 
    ./psql -U prddba -d prddb -h <HOSTNAME>
    Where,
    • <BIN_DIR>

      PostgreSQL bin directory

    • <USERNAME>

      The database username that has appropriate permissions to create user and roles.

    • <DATABASE_NAME>

      The application database name or postgres default database.

    • <HOSTNAME>

      IP address or hostname of the PostgreSQL server.

  3. Create superuser and replication user that you specified in the patroni_config.yaml file if they are not present in the existing PostgreSQL environment: 
    
CREATE USER <SUPERUSER_NAME> WITH SUPERUSER ENCRYPTED PASSWORD '<SUPERUSER_PASSWORD>';
CREATE USER <REPLICATION_USER> WITH REPLICATION ENCRYPTED PASSWORD '<REPLCIATION_PASSWORD>';
    Where,
    • <SUPERUSER_NAME>

      The name that you specified in the patroni_config.yaml file.

    • <SUPERUSER_PASSWORD>

      The password for superuser that is specified in the patroni_config.yaml file.

    • <REPLICATION_USER>

      The name that you specified in the patroni_config.yaml file for replication user.

    • <REPLCIATION_PASSWORD>

      The password for replication user who is specified in the patroni_config.yaml file.

    Note: If you back up your database by copying the entire data folder from an existing Cloud Application Business Insights server to Patroni node, then you must change the user permissions for the superuser and application user. Use the following commands to change the user permissions:
    ALTER ROLE <SUPERUSER_NAME> WITH SUPERUSER CREATEROLE REPLICATION BYPASSRLS CREATEDB;
ALTER ROLE prddba WITH NOSUPERUSER NOCREATEROLE NOREPLICATION NOBYPASSRLS NOCREATEDB;