Postgresql – Multiple Postgres Servers (one writer, multiple reader) with Shared Disk

clusterdatabase-replicationpostgresql

Here's the scenario:

One shard disk (Gluster)
Multiple Postgres servers

Requirements:

Use the shared disk to store the database files
Use a configuration which provide maximum efficiency

Findings so far,

It's possible to use a shared disk to store the data as this document says. But it also says that "Another issue is that the standby server should never access the shared storage while the primary server is running". That means all of servers (except the master one) are left unused which is almost unacceptable for us.
Since we are using shared disk there should be no replication. It's been found on this document that some configurations (Raw and Master/Slave modes) are good enough. But the other problem is that they might cause the above issue.

Problems:

There are a lot of documentation on the web which made me confused about their requirements and features. Is my understanding correct?
If so, is there any possibility to achieve this design (with pgpool or anyother tools)
If so, would you please name the tools and or the keywords so I can find more information.

Note (for those who are interested in closing questions as many as they can)- It happened before to me. Some say I'm looking for opinion based answers. In fact I'm not. What I'm looking is the name of technology or some sort of keywords, no matter what. So that by using them I can search for more information. It sometimes happens that you need to know some keywords for search and finding the information.

Thanks in advance.

Best Answer

It is not possible to run multiple PostgreSQL servers from the same data directory, even if all but one are read-only. Absolutely 100% unsupported. Cannot be done. Give up now.

Somebody might one day add such a feature but it'd involve major changes to PostgreSQL, as Pg relies heavily on shared memory and signals for inter-process synchronization. Also, the shared_buffers contain "dirty" buffers that aren't yet written out; these can be written out lazily because PostgreSQL knows all backends will read from there and only go to disk if the data isn't in shared_buffers.

It's possibly practical to do it with minor changes to PostgreSQL if all the servers are read-only, but I haven't investigated it as it's a pretty uninteresting use-case.

The references to shared storage you've seen are only for failover, not concurrent operation. The manual is quite specific that you need to ensure there's proper fencing in place to prevent concurrent access to the storage by multiple DB servers and that major corruption will result if you don't.

You're going to have to rely on replication or use another DB engine that supports shared storage (and deals with the resulting performance impact).

Separately, though: DBs are often I/O limited. Shared storage doesn't gain you anything if you now have two servers capable of 1000tps instead of one server that can do 2000. Or, given the overheads of synchronisation of a shared storage system w/o a low-latency bus (think Infiniband/Myrinet), more like two servers capable of 200tps each.

Related Solutions

Postgres Disk IO High – How to Reduce It Immediately

The 24MB shared_buffers setting is the conservative default, I'd say it needs to be quite a lot higher for a dedicated database with 16GB of RAM available. But yes, you'll have to restart the server to resize it. http://wiki.postgresql.org/wiki/Performance_Optimization is a good place to start for performance configuration guidelines. Setting the shared_buffers to 4GB or 6GB would seem more reasonable.

Note that on linux you need to adjust the kernel.shmmax sysctl setting (in /etc/sysctl.conf or just by writing /proc/sys/kernel/shmmax) to allocate a block of this much shared memory. If you don't you'll get an error specifying how much was requested, you have to set kernel.shmmax higher than that.

Since you have lots of memory, you might also consider setting the default work_mem higher, which will make things like sorts and hashes (group/order/distinct etc) tend to work in memory rather than using temp files. You don't need to restart the server to do this, just update the config file, reload the service and new sessions will get the new setting. The default work memory for a session is 1MB, you can calculate the maximum that may be used at a single time as work_mem * max_client_connections and estimate what impact that will have.

You should also increase effective_cache_size to indicate to the planner that the kernel FS layer is likely to be caching a lot of pages in memory outside of postgresql's shared buffers.

etc. etc. hope this gets you off to a good start.

Postgresql 9.3 Log Shipping on a Hot Standby

I also just ran into this issue. The key here is actually the archive_cleanup_command in the "recovery.conf" on the standby. The standby will run the archive_cleanup_command command when it is done processing a WAL segment from the primary, so at that point you know you can backup that WAL segment and all prior segments. In my "recovery.conf" I have:

archive_cleanup_command = '/var/lib/postgresql/wal_backup_mirror.sh "%r"'

The contents of that script are (simplified version):

CURRENT_WAL_FILE="$1"
for WAL_FILE in $(find /pg_logs/main -maxdepth 1 -type f | sort | awk "\$0 <= \"/pg_logs/main/${CURRENT_WAL_FILE}\""); do
  WAL_NAME=$(basename "$WAL_FILE")
  gzip -c "$WAL_FILE" > "/backups/wal/${WAL_NAME}.gz"
  #now upload the just created .gz to S3 or some other offsite storage

  rm -f "${WAL_FILE}"
done

Note here that I delete the WAL segment after backing it up to keep my log directory clean on the standby, but the one caveat there is to be careful of a cascaded replication setup, since a standby that is further down the chain might still need those files.

One final note, remember that backing up the WAL segments isn't sufficient, it has to be done in combination with some sort of regular full backup (pg_basebackup). We do full backups daily, and then just backup the WAL segments as needed throughout the day.

Best Answer

Related Solutions

Postgres Disk IO High – How to Reduce It Immediately

Postgresql 9.3 Log Shipping on a Hot Standby

Related Topic