Re: 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(at)yahoo(dot)co(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Glyn Astill <glynastill(at)yahoo(dot)co(dot)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(at)yahoo(dot)co(dot)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(at)yahoo(dot)co(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Glyn Astill <glynastill(at)yahoo(dot)co(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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(at)yahoo(dot)co(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Glyn Astill <glynastill(at)yahoo(dot)co(dot)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(dot)Caune(at)latnet(dot)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