Showing posts with label Replication. Show all posts
Showing posts with label Replication. Show all posts

Thursday, July 31, 2025

How to check if a PostgreSQL streaming replicate server is up-to-date with the primary

On the primary, run
SELECT pg_current_wal_lsn(); -- On the primary
In my case, the result was
 pg_current_wal_lsn
--------------------
 0/E015420
On the standby, run
SELECT pg_last_wal_replay_lsn();
In my case, the result was
pg_last_wal_replay_lsn
------------------------
 0/E015420

This means that

  • All WAL records have been received
  • All WAL recores have been replayed (not just flushed) on the standby
  • There is no lag between primary and standby
  • Friday, July 19, 2024

    How to check if your PostgreSQL server is part of a replication setup

    If the server is down, check the entries in postgresql.auto.conf.

    The following postgres.auto.conf is from a slave server:
    synchronous_commit = 'local'
    wal_level = 'replica'
    hot_standby = on
    hot_standby_feedback = 'on'
    primary_slot_name = 'stby1'
    max_connections = 636
    primary_conninfo      = 'host=pgserver01.oric.no port=5432 user=replicate password=mysecretpassword'
    promote_trigger_file  = '/tmp/MasterNow'
    
    The master server may have a very simple postgres.auto.conf file:
    # Do not edit this file manually!
    # It will be overwritten by the ALTER SYSTEM command.
    synchronous_commit = 'local'
    wal_level = 'replica'
    
    On both servers, if the parameter $PGDATA is set in your environment, use this statement to grep for the parameters:
    grep -E '^\s*(wal_level|hot_standby|hot_standby_feedback|primary_slot_name|primary_conninfo|promote_trigger_file|max_connections|synchronous_commit)\s*=' "$PGDATA/postgresql.auto.conf"
    

    If the server is up, use queries:

    1. On the primary server, check that data is shipped over to the replica server:
    [postgres@pgserver01.oric.no /pgdata/data]$ echo 'select usename,state,sync_state,sync_priority,client_hostname from pg_stat_replication;' | psql
      usename  |   state   | sync_state | sync_priority |        client_hostname
    -----------+-----------+------------+---------------+--------------------------------
     replicate | streaming | async      |             0 | pgserver02.oric.no
    (1 row)
    
    2. The following query should return f (false) on primary server, t (true) on the replica server:
    [postgres@pgserver01 /pgdata/data/log]$ echo "select pg_is_in_recovery();" | psql
     pg_is_in_recovery
    -------------------
     f
    (1 row)
    
    [postgres@pgserver02 /pgdata/data/log]$  echo "select pg_is_in_recovery();" | psql
     pg_is_in_recovery
    -------------------
     t
    (1 row)
    

    Friday, April 22, 2022

    How to set up replication between two postgreSQL servers

    Compared to Oracle, setup of a postgreSQL physical standby database is quite simple. With two identically provisioned posgreSQL 13 servers created, the below steps must be performed.
    Master: test01-sandbox-pgsql.mydomain.com
    Slave: test02-sandbox-pgsql.mydomain.com

    1. Configure the master database cluster As the postgres software owner, execute the following:
    echo "CREATE ROLE replicate WITH REPLICATION LOGIN password 'mypassword';" | psql
    echo "grant app_user to replicate;" | psql
    echo "alter system set wal_level = replica;" | psql
    echo "select pg_reload_conf();" | psql
    echo "alter system set synchronous_commit = local;" | psql
    echo "alter system set track_commit_timestamp = on;" |  psql
    
    2. Restart the postgres server
    systemctl restart postgresql-13.service
    
    3. Configure the slave database cluster

    As the root user, stop the postgres server
    systemctl stop postgresql-13.service
    
    4. Remove any files created in the deployment of the slave. They will be replaced anyway
    rm -rf /data/*
    5. take a base backup of the master database cluster
    pg_basebackup -h test01-sandbox-pgsql.mydomain.com -D /data/ -P -U replicate --wal-method=stream
    
    6. Set some parameters
    echo "hot_standby = on" >> /pgconfig/postgresql.auto.conf 
    
    echo "
    primary_conninfo      = 'host=masterhost.mydomain.com port=5432 user=replicate password=mypassword'
    promote_trigger_file  = '/tmp/MasterNow'
    " >  /pgconfig/postgresql.conf  
    
    7. Create a file necessary for the master/slave role determination
    touch /data/standby.signal
    
    8. Start the postgres server
    systemctl start postgresql-13.service
    9. Verification checks

    On the master, to check that everything works fine, use the dictionary view pg_stat_replication, which, according to the documentation "contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers."
     echo 'select usename,state,sync_state,sync_priority,client_hostname from pg_stat_replication;' | psql
      usename  |   state   | sync_state | sync_priority |          client_hostname
    -----------+-----------+------------+---------------+-----------------------------------
     replicate | streaming | async      |             0 | test02-sandbox-pgsql.mydomain.com
    (1 row)
    
    The check the slave status, use the dictionary view pg_stat_wal_receiver. According to the documentation the view "contain only one row, showing statistics about the WAL receiver from that receiver's connected server":
    psql
    psql (13.6)
    Type "help" for help.
    
    postgres=# \x
    Expanded display is on.
    postgres=# select status,sender_host,conninfo from pg_stat_wal_receiver;
    -[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    status      | streaming
    sender_host | test01-sandbox-pgsql.mydomain.com
    conninfo    | user=replicate password=******** channel_binding=prefer dbname=replication host=test01-sandbox-pgsql.mydomain.com port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
    
    Check that recovery is in progress:
     echo 'SELECT pg_is_in_recovery();' | psql
     pg_is_in_recovery
    -------------------
     t
    (1 row)
    
    The equivalent query on the master node would state "f" (false). Read more about
  • pg_stat_replication
  • pg_stat_wal_receiver

    Note that some important changes to replication were introduced in PostgreSQL version 12
  • Wednesday, April 20, 2022

    Oracle vs PostgreSQL replication terminology

    Functionality Oracle PostgreSQL
    A standby server that can accept connections and serves read-only queries Active Data Guard Hot Standby
    A standby server that cannot be connected to until it is promoted to a master server Physical Standby Server Warm Standby
    A data-modifying transaction is not considered committed until all servers have committed the transaction. Maximum Availability Synchronous
    Allow some delay between the time of a commit and its propagation to the other servers.
    Some transactions might be lost in the switch to a backup server
    load balanced servers might return slightly stale results.
    Maximum Performance Asynchronous
    Do not allow any transactions to be unprotected at any time Maximum Protection  

  • PostgreSQL 11 dokumentation here
  • Oracle 12cR2 documentation here
  • Wednesday, October 9, 2019

    Oracle streams being terminated with Oracle 18c


    As noted in the "Desupported features" section of the Oracle 18c documentation, Oracle Streams will be terminated in with the release of Oracle 18c.

    For replication, Oracle Corporation encourage their customers to use GoldenGate.