Postgresql – pg_commit_ts growing in postgres instance

postgresqlpostgresql-9.3

I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with little to no activity he is running out of space. He only have a 5 GB disk as of now with his POC out of which ~2 GB is occupied by wal files (no big fluctuation with the usage) however from the Host console I see that there pg_commit_ts folder keeps growing and is currently @1.7 GB

2.4G    pg_wal
1.7G    pg_commit_ts
96M     db/base
44M     /pg_xact

From postgres documentation I see that this is Subdirectory containing transaction commit timestamp data. I then noticed that he also have enabled "track_commit_timestamp" which collects commit time of transactions.

I'm trying to figure out 1. Is there any way to control retention on pg_commit_ts 2. Is this normal to have huge "pg_commit_ts" with the parameter enabled or does this have to do with their use case.

Please advise.

Best Answer

came to find out, that unless the XIDs are cleared by vacuum / autovacuum pg_commit_ts would not be cleared. So basically this instance needs attention on tuning autovacuum parameters to run autovacuum efficiently

Related Solutions

Postgresql – Why might the Postgres table be growing

Run this query:

SELECT * FROM pg_stat_activity ORDER BY xact_start NULLS LAST LIMIT 1;

If you get a row back where xact_start is not null and it's anything more than a few seconds old, that's your culprit. VACUUM cannot remove rows that have transaction_ids greater than or equal to the oldest open transaction in the system (even if it's in a completely different database). If you terminate that transaction and re-run the VACUUM, you should see more than 0 rows removable.

PostgreSQL 9.3 – Streaming Replication Using Two Servers

You seem to be trying to replicate from one server to another that wasn't set up using a copy of the original server. That's why:

database system identifier differs between the primary and standby. The primary's identifier is 6022019027749040119, the standby's identifier is 6033562405193904122.

Because each newly initdb'd PostgreSQL gets a new random system identifier. When you copy an existing PostgreSQL install, it keeps the same system identifier. That's how PostgreSQL can keep track of whether one server can replay WAL from another.

You can only use physical replication if the replica is a copy (file-system level backup e.g. pg_basebackup) of the master. See the manual's detailed coverage on replication for more information.

Update:

The instructions shown above should be fine, but they're not as clear as they could be.

The standby server's data directory is supposed to be replaced by the base backup you create at step 8, if it exists in the first place.

You can't make an existing PostgreSQL instance into a standby for another without replacing its data directory. You need a copy of the master's data directory to run a standby. A common way to set that up is to take an existing standby, delete its data directory, replace it with a copy of the master's data directory, and then configure it as a replication slave. That's what I think step 8 is supposed to be doing.

Instead of doing that I think you probably used an existing data directory for the slave and tried to start it up as a replica of the master. That will not work, and will result in the errors you showed.

The main PostgreSQL documentation on replication is the recommended and primary resource for information. I suggest going there first.

You might also want to check out repmgr, which helps automate replication and failover tasks.

Best Answer

Related Solutions

Postgresql – Why might the Postgres table be growing

PostgreSQL 9.3 – Streaming Replication Using Two Servers

Related Question