Hi all, I've come across a PostgreSQL set up where there are 2 servers, each with the same version of PostgreSQL on, both mounting the same SAN onto their respective file systems. It was intended that only 1 of the servers would be running an instance of PostgreSQL at a time as they both point to the same pgdata. This was dubbed a "high availability" set up, where if one server went down, they could start PostgreSQL on the other. (yes, I know what you're thinking) Now normally there is protection against 2 instances running only if the instances on the same server as it would reference shared memory. But in this case, neither server has access to the other's shared memory, so it has to rely on the pid file. But the pid file isn't enough by itself. In this set up, if someone were to inadvertently start up a Postgres instance on the 2nd server whilst the 1st was still running, it would do very bad things.
For example, when I set up the same scenario on my own network, it indeed let me start up the 2nd instance. I then tried setting up a table and generating lots of data for it, then... KABOOM: postgres=# create table things (id serial, things int); NOTICE: CREATE TABLE will create implicit sequence "things_id_seq" for serial column "things.id" CREATE TABLE postgres=# insert into things (things) select x from generate_series(1,500000) a(x); LOG: could not link file "pg_xlog/xlogtemp.28426" to "pg_xlog/000000010000000000000002" (initialization of log file 0, segment 2): Operation not supported STATEMENT: insert into things (things) select x from generate_series(1,500000) a(x); PANIC: could not open file "pg_xlog/000000010000000000000002" (log file 0, segment 2): No such file or directory STATEMENT: insert into things (things) select x from generate_series(1,500000) a(x); PANIC: could not open file "pg_xlog/000000010000000000000002" (log file 0, segment 2): No such file or directory PANIC: could not open file "pg_xlog/000000010000000000000002" (log file 0, segment 2): No such file or directory The connection to the server was lost. Attempting reset: LOG: server process (PID 28426) was terminated by signal 6: Abort trap LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. Failed. !> LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2011-09-08 19:04:47 BST LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 0/1755268 LOG: redo starts at 0/1755268 LOG: could not open file "pg_xlog/000000010000000000000002" (log file 0, segment 2): No such file or directory LOG: redo done at 0/1FFFFA8 LOG: last completed transaction was at log time 2011-09-08 19:05:14.098496+01 LOG: could not link file "pg_xlog/xlogtemp.28429" to "pg_xlog/000000010000000000000002" (initialization of log file 0, segment 2): Operation not supported PANIC: could not open file "pg_xlog/000000010000000000000002" (log file 0, segment 2): No such file or directory LOG: startup process (PID 28429) was terminated by signal 6: Abort trap LOG: aborting startup due to startup process failure Now obviously no-one should ever set up their system in such a fashion, but some have, as I've witnessed it. I suspect this is potentially the cause of their continued database corruption and outages, where expected WAL files aren't in the pg_xlog directory, so recovery can't finish, and clog files going missing etc. While I appreciate that this isn't a bug, and that no-one should actually be setting things up in this way, it does introduce the ability to hose one's own cluster without realising (until it's possibly too late). Would there be a way to prevent this abhorrent scenario from coming into existence? One idea is to have a configuration option to be strict about the presence of a pid file in the data directory, and force manual intervention, but I'm not sure this would solve the problem in most cases where this problem exists as someone would have had to specifically sought out the option and set it. It might also encourage some to just delete the pid file thinking that would make the nasty errors go away. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers