Thread: Warm standby system - FATAL: the database system is starting up
Hi people, I've set up a warm standby system using WAL shipping, partially following the guide here (My setup ios on two physically separate servers) http://archives.postgresql.org/sydpug/2006-10/msg00001.php I'm using an NFS share on the main server that is then mounted in fstab on the backup. I see in my log on the backup machine: FATAL: the database system is starting up Does this mean the backup is not working? I've left autovacuum on in postgresql.conf - could anythign like this be causing it? Or could I be missing part of the backup in some way? If I create lots of records on the main server, then the log on the backup looks like this: FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up `/mnt/archive/000000010000001A00000080' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000080" from archive `/mnt/archive/000000010000001A00000081' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000081" from archive `/mnt/archive/000000010000001A00000082' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000082" from archive `/mnt/archive/000000010000001A00000083' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000083" from archive `/mnt/archive/000000010000001A00000084' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000084" from archive `/mnt/archive/000000010000001A00000085' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000085" from archive FATAL: the database system is starting up `/mnt/archive/000000010000001A00000086' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000001A00000086" from archive FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up As far as I know theres nothing else trying to connect. Thanks Glyn __________________________________________________________ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
Glyn Astill <glynastill@yahoo.co.uk> writes: > I see in my log on the backup machine: > FATAL: the database system is starting up > Does this mean the backup is not working? No, it means something's trying to connect to the backup postmaster. regards, tom lane
Is there a way I can check what it is? I see no reason why anything would be trying to connect, any ideas? Could it be the autovacuum as I suggested? Also something I omitted to point out in my original post, the processes running on the machine (ps -ax) are as follows 3467 pts/0 S 0:00 /usr/local/pgsql/bin/postgres -D /data/postgres/ 3468 ? Ss 0:02 postgres: startup process 4858 ? S 0:00 /bin/bash /data/postgres/restore.sh /mnt/archive/000000010000001A00000087 pg_xlog/RECOVERYXLOG 6371 ? S 0:00 sleep 1 Notice the "postgres: startup process", does that just mean we're in WAL reading mode? Or does it mean it's not working. I know Tom, you said it will be working, but does this extra info change that? Thanks Glyn --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Glyn Astill <glynastill@yahoo.co.uk> writes: > > I see in my log on the backup machine: > > FATAL: the database system is starting up > > Does this mean the backup is not working? > > No, it means something's trying to connect to the backup > postmaster. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > ___________________________________________________________ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 21 Dec 2007 13:24:01 -0800 (PST) Glyn Astill <glynastill@yahoo.co.uk> wrote: > Is there a way I can check what it is? Change your log line prefix to show connections and ip addresses. Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHbDCcATb/zqfZUUQRAqabAKCCzgwW/JeKfEj6q6jkcSpp4JYV+ACfX8J4 BYwcImPTtVwyrsbw678q/9Q= =aHBj -----END PGP SIGNATURE-----
Glyn Astill <glynastill@yahoo.co.uk> writes: > Is there a way I can check what it is? log_connections would help... regards, tom lane
Hi chaps, Indeed you were correct! I enabled the good stuff in the log and sure enough one of the old testbed machines which is running postgres and slony was trying to connect! However I'd never even referenced the new machine on the old testbed so how on earth could it have been trying to connect? Also (maybe I'm a bit slow or something" should the WAL backup machine be showing "postgres: startup process"? Thanks Glyn --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Glyn Astill <glynastill@yahoo.co.uk> writes: > > Is there a way I can check what it is? > > log_connections would help... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire > to > choose an index scan if your joining column's datatypes do > not > match > __________________________________________________________ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
I would recommend that whenever you are trying to recover a database (be it WAL shipping or any other method), first change the port it is listening on, to something that your apps wouldn't assume the DB to be running on, and then start the recovery.
This way, even if there's a rogue application that you forgot to shut down, your recovering database wouldn't be bothered about it.
Then, when you are finished recovery, just change back the port to your default, and restart the server.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
This way, even if there's a rogue application that you forgot to shut down, your recovering database wouldn't be bothered about it.
Then, when you are finished recovery, just change back the port to your default, and restart the server.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
On Dec 21, 2007 1:24 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
Is there a way I can check what it is?
I see no reason why anything would be trying to connect, any ideas?
Could it be the autovacuum as I suggested?
Also something I omitted to point out in my original post, the
processes running on the machine (ps -ax) are as follows
3467 pts/0 S 0:00 /usr/local/pgsql/bin/postgres -D
/data/postgres/
3468 ? Ss 0:02 postgres: startup process
4858 ? S 0:00 /bin/bash /data/postgres/restore.sh
/mnt/archive/000000010000001A00000087 pg_xlog/RECOVERYXLOG
6371 ? S 0:00 sleep 1
Notice the "postgres: startup process", does that just mean we're in
WAL reading mode? Or does it mean it's not working.
I know Tom, you said it will be working, but does this extra info
change that?
Thanks
Glyn
--- Tom Lane <tgl@sss.pgh.pa.us > wrote:
> Glyn Astill <glynastill@yahoo.co.uk> writes:
> > I see in my log on the backup machine:
> > FATAL: the database system is starting up
> > Does this mean the backup is not working?
>
> No, it means something's trying to connect to the backup
> postmaster.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>___________________________________________________________
Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Glyn Astill wrote: > FATAL: the database system is starting up > FATAL: the database system is starting up > FATAL: the database system is starting up > > > As far as I know theres nothing else trying to connect. I think you are using FreeBSD, and default pg_ctl flags in rc.d contains "-w" option, which wait for start/stop to complete. It check this by connecting to postmaster. change postgresql_flags in /etc/rc.conf to: postgresql_flags="-s -m fast" or postgresql_flags="-s -m smart"
Hi Artis, I'm using debian. We discovered another postgres on the network was trying to connect. What about the "postgres: startup process" ? Should this always be visibile on a system reading WALs? Nobody has answered that yet. Glyn --- Artis Caune <Artis.Caune@latnet.lv> wrote: > Glyn Astill wrote: > > FATAL: the database system is starting up > > FATAL: the database system is starting up > > FATAL: the database system is starting up > > > > > > As far as I know theres nothing else trying to connect. > > > I think you are using FreeBSD, and default pg_ctl flags in rc.d > contains > "-w" option, which wait for start/stop to complete. It check this > by > connecting to postmaster. > > change postgresql_flags in /etc/rc.conf to: > postgresql_flags="-s -m fast" > or > postgresql_flags="-s -m smart" > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > __________________________________________________________ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
Glyn Astill wrote: > What about the "postgres: startup process" ? Should this always be > visibile on a system reading WALs? Nobody has answered that yet. Yes. In fact it is the only process that reads WAL. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support