[GENERAL] Archiving while idle every archive_timeout with wal_level hot_standby

I'm seeing Postgres 9.4.4 archive while idle every archive_timeout when I set ``wal_level hot_standby``: $ initdb testpg > $ cat << 'EOF' >> testpg/postgresql.conf > > wal_level = hot_standby > > archive_mode = on > > archive_timeout = 10 > > checkpoint_timeout = 1h > > archive_command = 'echo $(d

[GENERAL] Synchronous archiving

I have a relatively small database with not many writes. However, I'm keen to ensure that those writes I receive are not lost. Archiving gets me most of the way there, but it is asynchronous. Is there any way to archive individual WAL records without running a replica? Running in Amazon's cloud, I

[GENERAL] Avoid WAL archiving when idle?

I have WAL archiving setup on Postgres 9.3.2 using WAL-E on CentOS 6.4 using the postgresql.org RPM. This is working fine, except I see a lot of spurious activity in the S3 bucket with wal files being backed up every 5 minutes even when the database is idle. This can make restoring to a dev server

[GENERAL] json_array_elements_text?

Hello, I'm trying to unpack a json array into it's constituent text values so I can join them to a table. I can successfully unpack json values, but am having trouble converting these to text so I can cast them to the UUIDs needed for the join. http://dba.stackexchange.com/questions/54283/how-to-

[GENERAL] Building an invalidation queue in Postgres

I'd like to cache parts of my database locally on each client. To keep those caches in sync I'd like to implement an invalidation queue. A naïve approach would be to simply create a table of (txn_id, invalidated_object_ids), then have the clients query this table for txn_ids > last_queried_txn_id.