Verifying that the database is replicating

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
There are some simple ways to ensure that the main database is properly replicating to the hot standby. You will need to do most of this observation on the hot standby machine.
  • Observe the Postgres log roll forward the changes
  • Looking at the replication logs to see that they are created and then removed
  • Make some changes to the main database and then using pgAdmin to verify that the changes are in the backup in real time

Using pgAdmin to see Replication Status

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
On the main server, you can run the following query in pgAdmin to look at the state of replication:

select * from pg_stat_replication

This should give a row back that says the server is 'streaming'. You can repeat the query and the numbers will change, indicating that replication is proceeding.

If you see a different result like:

  • The status is 'catch-up', then the backup machine is not up to date with the main server, which may indicate issues.
  • There is no row or data in response to this command, it means replication is not running, and your main disk will be slowly filling up -- you will run out of space. Get the properties of the disk on the main server to see how fast it it filling up and send us a support ticket. it often just means restarting one or both servers, or verifying ip addresses on the two machines.

If the query gives no rows back (or some other status), then things may not be set up right, or replication has not yet begun because archive log files are being recovered on the Hot Standby Server.

Using pgAdmin to Watch Transactions

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.

Alternatives

Data replicates from the main server to the Hot Standby immediately. If there is a change to the main database, it should appear in the hot standby immediately after that.

One way to verify changes is to have the box office sell a ticket to a patron using Theatre Manager. Before the sale, there should be no transaction history for that order; after the sale, there should be some ticket transactions.

Perform this test daily or weekly to make sure that the database is replicating.

Occasionally, if you take the standby down for maintenance, it may get a bit behind. In that case, you may not see changes until the archive files have been fully recovered and you may need to look at the standby server's Postgres log files for progress. The archives are created periodically as per the frequency that you set in the archive_timeout parameter)

Verifying Simultaneous Activity on Both Servers

Follow these steps to see that data is being migrated:

  1. Start pgAdmin on the main server, point to the main database and start an SQL session
  2. Start pgAdmin on the Hot Standby server, point to the standby database (it is a different IP address) and start an SQL session
  3. On both machines, enter the SQL below into pgAdmin to verify the last 10 transactions

    select * from f_transaction order by t_seq desc limit 10
  4. Log in to Theatre Manager on any workstation (or have anybody sell a ticket)
  5. in pgAdmin on the MAIN database, rerun the SQL above. There should now be a new login transaction or transactions that match the number of tickets sold.
  6. In pgAdmin on the Hot Standby database, rerun the SQL above.
    • There should be the same transactions visible on the hot standby right away.
    • That means the servers have replicated the data.
  7. In Theatre Manager, release the tickets or log out.
  8. in pgAdmin on the MAIN database, rerun the SQL above. There should now be MORE transactions representing your activity.
  9. In pgAdmin on the Hot Standby database, rerun the SQL above.
    • You should see the new transactions on the standby server, meaning the servers have again replicated the data.

Reviewing Postgres Log File for Replication Entries

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
The data is replicating from the Main server to the hot standby periodically, as per the frequency that you set in the archive_timeout parameter. If there is a change to the main database, it should appear in the hot standby very shortly after that.

You should go to the log files once in a while and ensure that it is replicating and able to see/connect to the other database. After the tail command, you should see that the server entered standby mode and that is connecting to the main server.

Even after setting up the replication to the hot standby server, you should check this log periodically to make sure it continues to work.

su - postgres
Enter the Postgres password
cd /Library/PostgreSQL/9.6/data/pg_log
ls -la (to see a list of logs)
tail -f name-of-most-recent-log-file
The most recent log file will have today's date in it and the 'tail -f' command will continue to display the changes to the file as they occur.

The example below shows the startup of the standby server after first being created (or being stopped for a period of time). The log files are consumed and, finally, replication starts. Once replication starts, no further 'restore' messages will appear in the logs -- you can then use the pgAdmin query method to view the status of replication on the main server.

Inspect the Archive Directory

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
The data is replicating from the main server to the hot standby periodically as per the frequency that you set in the archive_timeout parameter. If there is a change to the main database, it should appear in the hot standby very shortly after that.

You can go to the replication folder and see that new files appear once a minute and old ones go away.

cd /Library/PostgreSQL/9.6/archive
ls -la (to see a list of recovery files).
The file names will be very long with digits in them. The name of them will also increase numerically.
For example:
  • 000000010000000000000055 will have as the next file 000000010000000000000056 - about 60 seconds later or whatever time period you set in the archive_timeout command
  • Eventually 000000010000000000000055 will disappear as it is processed by the hot standby
  • Generally, on the server, there should be only a few of these files (5 to 10). if there are more, the server may be a little busy. If they do not decrease, replication is not working right or the server is overloaded.
  • and the above will repeat.