pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutomatic restore corruption problem
11+ messages / 5 participants
[nested] [flat]
* Automatic restore corruption problem
@ 2015-07-10 12:59 Matthieu Lejeune <matthieu.lejeune@exxoss.com>
0 siblings, 2 replies; 11+ messages in thread
From: Matthieu Lejeune @ 2015-07-10 12:59 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I have a script for restoring a database every night to an other
postgresql database
root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
#/bin/bash
/etc/init.d/postgresql stop
mv /var/log/postgresql/postgresql-9.3-main.log
/var/log/postgresql/postgresql-9.3-main.log.old
cd /var/lib/postgresql/9.3/main
psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
pg_start_backup('sync');"
rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/*
/var/lib/postgresql/9.3/main/
rm backup_label
chown -R postgres:postgres *
psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
pg_stop_backup();"
/etc/init.d/postgresql start
chmod 777 /var/log/postgresql/postgresql-9.3-main.log
psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
root@p2prddnmdbc:~#
But during the day when the user are using the new database we got error
like this :
2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
"base/16386/14064061.1": read only 0 of 8192 bytes
2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
"base/16386/14064061.1": read only 0 of 8192 bytes
I have check the : filesystem on the vm, on the HW SAN,...
Any idea to fix this problem?
Thanks
Matthieu
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-10 13:30 michael@sqlexec.com <michael@sqlexec.com>
parent: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
1 sibling, 0 replies; 11+ messages in thread
From: michael@sqlexec.com @ 2015-07-10 13:30 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: pgsql-admin
Perhaps you did not start up the new database in restore mode pointing
to the WAL logs that were generated during your rsync operation that
were not captured.
Regards,
Michael
> Matthieu Lejeune <mailto:matthieu.lejeune@exxoss.com>
> Friday, July 10, 2015 8:59 AM
> Hi all,
>
> I have a script for restoring a database every night to an other
> postgresql database
>
> root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
> #/bin/bash
> /etc/init.d/postgresql stop
> mv /var/log/postgresql/postgresql-9.3-main.log
> /var/log/postgresql/postgresql-9.3-main.log.old
> cd /var/lib/postgresql/9.3/main
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
> pg_start_backup('sync');"
> rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/*
> /var/lib/postgresql/9.3/main/
> rm backup_label
> chown -R postgres:postgres *
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
> pg_stop_backup();"
> /etc/init.d/postgresql start
> chmod 777 /var/log/postgresql/postgresql-9.3-main.log
> psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
> psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
> root@p2prddnmdbc:~#
>
>
> But during the day when the user are using the new database we got
> error like this :
>
> 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
> "base/16386/14064061.1": read only 0 of 8192 bytes
> 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
> "base/16386/14064061.1": read only 0 of 8192 bytes
>
> I have check the : filesystem on the vm, on the HW SAN,...
>
> Any idea to fix this problem?
>
> Thanks
>
> Matthieu
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-10 14:07 Guillaume Lelarge <guillaume@lelarge.info>
parent: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
1 sibling, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2015-07-10 14:07 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: pgsql-admin
Hi,
Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <matthieu.lejeune@exxoss.com>
a écrit :
>
> Hi all,
>
> I have a script for restoring a database every night to an other
postgresql database
>
> root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
> #/bin/bash
> /etc/init.d/postgresql stop
> mv /var/log/postgresql/postgresql-9.3-main.log
/var/log/postgresql/postgresql-9.3-main.log.old
> cd /var/lib/postgresql/9.3/main
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
pg_start_backup('sync');"
> rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/*
/var/lib/postgresql/9.3/main/
> rm backup_label
> chown -R postgres:postgres *
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
pg_stop_backup();"
> /etc/init.d/postgresql start
> chmod 777 /var/log/postgresql/postgresql-9.3-main.log
> psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
> psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
> root@p2prddnmdbc:~#
>
>
> But during the day when the user are using the new database we got error
like this :
>
> 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
"base/16386/14064061.1": read only 0 of 8192 bytes
> 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
"base/16386/14064061.1": read only 0 of 8192 bytes
>
> I have check the : filesystem on the vm, on the HW SAN,...
>
> Any idea to fix this problem?
Sure. Don't remove the backup_label file, and add the recovery.conf file.
--
Guillaume
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-10 14:46 Keith <keith@keithf4.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Keith @ 2015-07-10 14:46 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; pgsql-admin
A recent, relevant post
http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge <guillaume@lelarge.info>
wrote:
> Hi,
>
> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <matthieu.lejeune@exxoss.com>
> a écrit :
> >
> > Hi all,
> >
> > I have a script for restoring a database every night to an other
> postgresql database
> >
> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
> > #/bin/bash
> > /etc/init.d/postgresql stop
> > mv /var/log/postgresql/postgresql-9.3-main.log
> /var/log/postgresql/postgresql-9.3-main.log.old
> > cd /var/lib/postgresql/9.3/main
> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
> pg_start_backup('sync');"
> > rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/*
> /var/lib/postgresql/9.3/main/
> > rm backup_label
> > chown -R postgres:postgres *
> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
> pg_stop_backup();"
> > /etc/init.d/postgresql start
> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
> > root@p2prddnmdbc:~#
> >
> >
> > But during the day when the user are using the new database we got error
> like this :
> >
> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
> "base/16386/14064061.1": read only 0 of 8192 bytes
> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
> "base/16386/14064061.1": read only 0 of 8192 bytes
> >
> > I have check the : filesystem on the vm, on the HW SAN,...
> >
> > Any idea to fix this problem?
>
> Sure. Don't remove the backup_label file, and add the recovery.conf file.
>
> --
> Guillaume
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-12 08:18 Matthieu Lejeune <matthieu.lejeune@exxoss.com>
parent: Keith <keith@keithf4.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matthieu Lejeune @ 2015-07-12 08:18 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; pgsql-admin
Hi thank for your reply
My target is to give a database for buisness testing query and they are
modify the database during the buisness day.
Now I got this error if I keep the file backup_label :
root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.3 database server: main[....] The
PostgreSQL server failed to start. Please check the log output:
2015-07-12 10:12:45 CEST LOG: database system was shut down at
2015-07-12 10:07:10 CEST 2015-07-12 10:12:45 CEST LOG: invalid
checkpoint record 2015-07-12 10:12:45 CEST FATAL: could not locate
required checkpoint record 2015-07-12 10:12:45 CEST HINT: If you are not
restoring from a backup, try removing the file
"/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45 CEST
LOG: startup process (PID 28492) exited with exit code 1 2015-07-12
10:12:45 CEST LOG: abo[FAIL startup due to startup process failure ...
failed!
failed!
If I put the recovery.conf the database is waiting for the wal to
relaunch the replication.
postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00
/usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_
postgres: startup process waiting for 0000000100000178000000B9
root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres
postgres@p2prddnmdbc:~$ psql energycomm
psql: FATAL: the database system is starting up
Have you got an idea to stop the replication process and start the
database ?
Kind regards
Matthieu
Le 10/07/15 16:46, Keith a écrit :
> A recent, relevant post
>
> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>
> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge
> <guillaume@lelarge.info <mailto:guillaume@lelarge.info>> wrote:
>
> Hi,
>
> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune"
> <matthieu.lejeune@exxoss.com <mailto:matthieu.lejeune@exxoss.com>>
> a écrit :
> >
> > Hi all,
> >
> > I have a script for restoring a database every night to an other
> postgresql database
> >
> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
> > #/bin/bash
> > /etc/init.d/postgresql stop
> > mv /var/log/postgresql/postgresql-9.3-main.log
> /var/log/postgresql/postgresql-9.3-main.log.old
> > cd /var/lib/postgresql/9.3/main
> > psql --host=p2prddnmdbm --username=replicator postgres -c
> "SELECT pg_start_backup('sync');"
> > rsync -av --delete
> root@10.10.11.1:/var/lib/postgresql/9.3/main/*
> /var/lib/postgresql/9.3/main/
> > rm backup_label
> > chown -R postgres:postgres *
> > psql --host=p2prddnmdbm --username=replicator postgres -c
> "SELECT pg_stop_backup();"
> > /etc/init.d/postgresql start
> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
> > root@p2prddnmdbc:~#
> >
> >
> > But during the day when the user are using the new database we
> got error like this :
> >
> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in
> file "base/16386/14064061.1": read only 0 of 8192 bytes
> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in
> file "base/16386/14064061.1": read only 0 of 8192 bytes
> >
> > I have check the : filesystem on the vm, on the HW SAN,...
> >
> > Any idea to fix this problem?
>
> Sure. Don't remove the backup_label file, and add the
> recovery.conf file.
>
> --
> Guillaume
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-12 11:46 Guillaume Lelarge <guillaume@lelarge.info>
parent: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
0 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2015-07-12 11:46 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Hi,
2015-07-12 10:18 GMT+02:00 Matthieu Lejeune <matthieu.lejeune@exxoss.com>:
> Hi thank for your reply
>
> My target is to give a database for buisness testing query and they are
> modify the database during the buisness day.
>
> Now I got this error if I keep the file backup_label :
>
> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql
> start
> [....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL
> server failed to start. Please check the log output: 2015-07-12 10:12:45
> CEST LOG: database system was shut down at 2015-07-12 10:07:10 CEST
> 2015-07-12 10:12:45 CEST LOG: invalid checkpoint record 2015-07-12 10:12:45
> CEST FATAL: could not locate required checkpoint record 2015-07-12 10:12:45
> CEST HINT: If you are not restoring from a backup, try removing the file
> "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45 CEST LOG:
> startup process (PID 28492) exited with exit code 1 2015-07-12 10:12:45
> CEST LOG: abo[FAIL startup due to startup process failure ... failed!
> failed!
>
>
> If I put the recovery.conf the database is waiting for the wal to relaunch
> the replication.
>
> postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00
> /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
> postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_
> postgres: startup process waiting for 0000000100000178000000B9
> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres
> postgres@p2prddnmdbc:~$ psql energycomm
> psql: FATAL: the database system is starting up
>
> Have you got an idea to stop the replication process and start the
> database ?
>
>
What did you put in the recovery.conf file? (hint: standby_mode must be off)
> Kind regards
> Matthieu
>
> Le 10/07/15 16:46, Keith a écrit :
>
> A recent, relevant post
>
>
> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>
> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge <
> <guillaume@lelarge.info>guillaume@lelarge.info> wrote:
>
>> Hi,
>>
>> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <matthieu.lejeune@exxoss.com>
>> a écrit :
>> >
>> > Hi all,
>> >
>> > I have a script for restoring a database every night to an other
>> postgresql database
>> >
>> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
>> > #/bin/bash
>> > /etc/init.d/postgresql stop
>> > mv /var/log/postgresql/postgresql-9.3-main.log
>> /var/log/postgresql/postgresql-9.3-main.log.old
>> > cd /var/lib/postgresql/9.3/main
>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>> pg_start_backup('sync');"
>> > rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/*
>> /var/lib/postgresql/9.3/main/
>> > rm backup_label
>> > chown -R postgres:postgres *
>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>> pg_stop_backup();"
>> > /etc/init.d/postgresql start
>> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
>> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
>> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
>> > root@p2prddnmdbc:~#
>> >
>> >
>> > But during the day when the user are using the new database we got
>> error like this :
>> >
>> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
>> "base/16386/14064061.1": read only 0 of 8192 bytes
>> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
>> "base/16386/14064061.1": read only 0 of 8192 bytes
>> >
>> > I have check the : filesystem on the vm, on the HW SAN,...
>> >
>> > Any idea to fix this problem?
>>
>> Sure. Don't remove the backup_label file, and add the recovery.conf file.
>>
>> --
>> Guillaume
>>
>
>
>
>
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-14 06:09 Matthieu Lejeune <matthieu.lejeune@exxoss.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Matthieu Lejeune @ 2015-07-14 06:09 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Hi,
I had no recovery.conf on this server because I launch my replication
every night I need a H-24 copy database.
This is my recovery.conf
root@p2prddnmdbc:/var/lib/postgresql# cat recovery.conf
standby_mode = 'off'
primary_conninfo = 'host=10.10.11.1 port=5432 user=replicator
password=XXXXXX'
trigger_file = '/var/lib/postgresql/9.1/main/trigger'
restore_command = 'cp /mnt/p2prddnmdbm_pg_xlog/%f %p'
root@p2prddnmdbc:/var/lib/postgresql#
But with or without a recovery.conf file I can't start the database
service :
root@p2prddnmdbc:/var/lib/postgresql# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.3 database server: main[....] The
PostgreSQL server failed to start. Please check the log output:
2015-07-14 08:02:59 CEST LOG: database system was interrupted; last
known up at 2015-07-13 23:33:46 CEST 2015-07-14 08:02:59 CEST LOG:
invalid checkpoint record 2015-07-14 08:02:59 CEST FATAL: could not
locate required checkpoint record 2015-07-14 08:02:59 CEST HINT: If you
are not restoring from a backup, try removing the file
"/var/lib/postgresql/9.3/main/backup_label". 2015-07-14 08:02:59 CEST
LOG: startup process (PID 24617) exited with exit co[FAIL2015-07-14
08:02:59 CEST LOG: aborting startup due to startup process failure ...
failed!
failed!
Thanks
Matthieu
Le 12/07/15 13:46, Guillaume Lelarge a écrit :
> Hi,
>
> 2015-07-12 10:18 GMT+02:00 Matthieu Lejeune
> <matthieu.lejeune@exxoss.com <mailto:matthieu.lejeune@exxoss.com>>:
>
> Hi thank for your reply
>
> My target is to give a database for buisness testing query and
> they are modify the database during the buisness day.
>
> Now I got this error if I keep the file backup_label :
>
> root@p2prddnmdbc:/var/lib/postgresql/9.3/main#
> <mailto:root@p2prddnmdbc:/var/lib/postgresql/9.3/main#>
> /etc/init.d/postgresql start
> [....] Starting PostgreSQL 9.3 database server: main[....] The
> PostgreSQL server failed to start. Please check the log output:
> 2015-07-12 10:12:45 CEST LOG: database system was shut down at
> 2015-07-12 10:07:10 CEST 2015-07-12 10:12:45 CEST LOG: invalid
> checkpoint record 2015-07-12 10:12:45 CEST FATAL: could not locate
> required checkpoint record 2015-07-12 10:12:45 CEST HINT: If you
> are not restoring from a backup, try removing the file
> "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45
> CEST LOG: startup process (PID 28492) exited with exit code 1
> 2015-07-12 10:12:45 CEST LOG: abo[FAIL startup due to startup
> process failure ... failed!
> failed!
>
>
> If I put the recovery.conf the database is waiting for the wal to
> relaunch the replication.
>
> postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00
> /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
> postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_
> postgres: startup process waiting for 0000000100000178000000B9
> root@p2prddnmdbc:/var/lib/postgresql/9.3/main#
> <mailto:root@p2prddnmdbc:/var/lib/postgresql/9.3/main#> su - postgres
> postgres@p2prddnmdbc:~$ psql energycomm
> psql: FATAL: the database system is starting up
>
> Have you got an idea to stop the replication process and start the
> database ?
>
>
> What did you put in the recovery.conf file? (hint: standby_mode must
> be off)
>
>
> Kind regards
> Matthieu
>
> Le 10/07/15 16:46, Keith a écrit :
>> A recent, relevant post
>>
>> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>>
>> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge
>> <guillaume@lelarge.info <mailto:guillaume@lelarge.info>> wrote:
>>
>> Hi,
>>
>> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune"
>> <matthieu.lejeune@exxoss.com
>> <mailto:matthieu.lejeune@exxoss.com>> a écrit :
>> >
>> > Hi all,
>> >
>> > I have a script for restoring a database every night to an
>> other postgresql database
>> >
>> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
>> > #/bin/bash
>> > /etc/init.d/postgresql stop
>> > mv /var/log/postgresql/postgresql-9.3-main.log
>> /var/log/postgresql/postgresql-9.3-main.log.old
>> > cd /var/lib/postgresql/9.3/main
>> > psql --host=p2prddnmdbm --username=replicator postgres -c
>> "SELECT pg_start_backup('sync');"
>> > rsync -av --delete
>> root@10.10.11.1:/var/lib/postgresql/9.3/main/*
>> <mailto:root@10.10.11.1:/var/lib/postgresql/9.3/main/*>
>> /var/lib/postgresql/9.3/main/
>> > rm backup_label
>> > chown -R postgres:postgres *
>> > psql --host=p2prddnmdbm --username=replicator postgres -c
>> "SELECT pg_stop_backup();"
>> > /etc/init.d/postgresql start
>> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
>> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
>> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
>> > root@p2prddnmdbc:~#
>> >
>> >
>> > But during the day when the user are using the new database
>> we got error like this :
>> >
>> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985
>> in file "base/16386/14064061.1": read only 0 of 8192 bytes
>> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801
>> in file "base/16386/14064061.1": read only 0 of 8192 bytes
>> >
>> > I have check the : filesystem on the vm, on the HW SAN,...
>> >
>> > Any idea to fix this problem?
>>
>> Sure. Don't remove the backup_label file, and add the
>> recovery.conf file.
>>
>> --
>> Guillaume
>>
>>
>
>
>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-14 06:50 Guillaume Lelarge <guillaume@lelarge.info>
parent: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
0 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2015-07-14 06:50 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
2015-07-14 8:09 GMT+02:00 Matthieu Lejeune <matthieu.lejeune@exxoss.com>:
> Hi,
>
> I had no recovery.conf on this server because I launch my replication
> every night I need a H-24 copy database.
>
>
On your first email, you said: "I have a script for restoring a database
every night to an other postgresql database". A restore is not replication,
even it's a PITR restore.
Still on your first email, you execute pg_start_backup() and
pg_stop_backup() on p2prddnmdbm, and the rsync on 10.10.11.1. I suppose
p2prddnmdbm and 10.10.11.1 are the same server?
This is my recovery.conf
> root@p2prddnmdbc:/var/lib/postgresql# cat recovery.conf
> standby_mode = 'off'
> primary_conninfo = 'host=10.10.11.1 port=5432 user=replicator
> password=XXXXXX'
> trigger_file = '/var/lib/postgresql/9.1/main/trigger'
> restore_command = 'cp /mnt/p2prddnmdbm_pg_xlog/%f %p'
> root@p2prddnmdbc:/var/lib/postgresql#
>
>
You don't need primary_conninfo if you only want to restore your database.
Though that isn't your issue right now.
> But with or without a recovery.conf file I can't start the database
> service :
> root@p2prddnmdbc:/var/lib/postgresql# /etc/init.d/postgresql start
> [....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL
> server failed to start. Please check the log output: 2015-07-14 08:02:59
> CEST LOG: database system was interrupted; last known up at 2015-07-13
> 23:33:46 CEST 2015-07-14 08:02:59 CEST LOG: invalid checkpoint record
> 2015-07-14 08:02:59 CEST FATAL: could not locate required checkpoint record
> 2015-07-14 08:02:59 CEST HINT: If you are not restoring from a backup, try
> removing the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-14
> 08:02:59 CEST LOG: startup process (PID 24617) exited with exit
> co[FAIL2015-07-14 08:02:59 CEST LOG: aborting startup due to startup
> process failure ... failed!
> failed!
>
>
In your previous email, you had the startup process waiting for a file.
These logs can't be the good ones.
What you should probably do is tell us exactly what you want to do, and
state what you do right now, and what logs you get, and what processes are
on the server. That would help us to help you.
Thanks
> Matthieu
>
> Le 12/07/15 13:46, Guillaume Lelarge a écrit :
>
> Hi,
>
> 2015-07-12 10:18 GMT+02:00 Matthieu Lejeune <
> <matthieu.lejeune@exxoss.com>matthieu.lejeune@exxoss.com>:
>
>> Hi thank for your reply
>>
>> My target is to give a database for buisness testing query and they are
>> modify the database during the buisness day.
>>
>> Now I got this error if I keep the file backup_label :
>>
>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql
>> start
>> [....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL
>> server failed to start. Please check the log output: 2015-07-12 10:12:45
>> CEST LOG: database system was shut down at 2015-07-12 10:07:10 CEST
>> 2015-07-12 10:12:45 CEST LOG: invalid checkpoint record 2015-07-12 10:12:45
>> CEST FATAL: could not locate required checkpoint record 2015-07-12 10:12:45
>> CEST HINT: If you are not restoring from a backup, try removing the file
>> "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45 CEST LOG:
>> startup process (PID 28492) exited with exit code 1 2015-07-12 10:12:45
>> CEST LOG: abo[FAIL startup due to startup process failure ... failed!
>> failed!
>>
>>
>> If I put the recovery.conf the database is waiting for the wal to
>> relaunch the replication.
>>
>> postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00
>> /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
>> postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_
>> postgres: startup process waiting for 0000000100000178000000B9
>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres
>> postgres@p2prddnmdbc:~$ psql energycomm
>> psql: FATAL: the database system is starting up
>>
>> Have you got an idea to stop the replication process and start the
>> database ?
>>
>>
> What did you put in the recovery.conf file? (hint: standby_mode must be
> off)
>
>
>
>> Kind regards
>> Matthieu
>>
>> Le 10/07/15 16:46, Keith a écrit :
>>
>> A recent, relevant post
>>
>>
>> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>>
>> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge <
>> <guillaume@lelarge.info>guillaume@lelarge.info> wrote:
>>
>>> Hi,
>>>
>>> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <
>>> <matthieu.lejeune@exxoss.com>matthieu.lejeune@exxoss.com> a écrit :
>>> >
>>> > Hi all,
>>> >
>>> > I have a script for restoring a database every night to an other
>>> postgresql database
>>> >
>>> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
>>> > #/bin/bash
>>> > /etc/init.d/postgresql stop
>>> > mv /var/log/postgresql/postgresql-9.3-main.log
>>> /var/log/postgresql/postgresql-9.3-main.log.old
>>> > cd /var/lib/postgresql/9.3/main
>>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>>> pg_start_backup('sync');"
>>> > rsync -av --delete <root@10.10.11.1:/var/lib/postgresql/9.3/main/*>
>>> root@10.10.11.1:/var/lib/postgresql/9.3/main/*
>>> /var/lib/postgresql/9.3/main/
>>> > rm backup_label
>>> > chown -R postgres:postgres *
>>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>>> pg_stop_backup();"
>>> > /etc/init.d/postgresql start
>>> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
>>> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
>>> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
>>> > root@p2prddnmdbc:~#
>>> >
>>> >
>>> > But during the day when the user are using the new database we got
>>> error like this :
>>> >
>>> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
>>> "base/16386/14064061.1": read only 0 of 8192 bytes
>>> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
>>> "base/16386/14064061.1": read only 0 of 8192 bytes
>>> >
>>> > I have check the : filesystem on the vm, on the HW SAN,...
>>> >
>>> > Any idea to fix this problem?
>>>
>>> Sure. Don't remove the backup_label file, and add the recovery.conf file.
>>>
>>> --
>>> Guillaume
>>>
>>
>>
>>
>>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
>
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-14 10:32 Matthieu Lejeune <matthieu.lejeune@exxoss.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Matthieu Lejeune @ 2015-07-14 10:32 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Hi,
The master server : 10.10.11.1 is p2prddnmdbm
I got a real time slave with the recovery process : p2prddnmdbs
I have a third copy with a H-24 data : p2prddnmdbc
On this server I need to have the copy of the master at 23H00 every day.
So I'm using the script send before.
During the day the buisness modify the database on server p2prddnmdbc
and that's why I use a rsync --delete every night to get a clean copy of
the prod server.
So what's the best way to do the restore ?
Thanks
Matthieu
Le 14/07/15 08:50, Guillaume Lelarge a écrit :
> 2015-07-14 8:09 GMT+02:00 Matthieu Lejeune
> <matthieu.lejeune@exxoss.com <mailto:matthieu.lejeune@exxoss.com>>:
>
> Hi,
>
> I had no recovery.conf on this server because I launch my
> replication every night I need a H-24 copy database.
>
>
> On your first email, you said: "I have a script for restoring a
> database every night to an other postgresql database". A restore is
> not replication, even it's a PITR restore.
>
> Still on your first email, you execute pg_start_backup() and
> pg_stop_backup() on p2prddnmdbm, and the rsync on 10.10.11.1. I
> suppose p2prddnmdbm and 10.10.11.1 are the same server?
>
> This is my recovery.conf
> root@p2prddnmdbc:/var/lib/postgresql# cat recovery.conf
> standby_mode = 'off'
> primary_conninfo = 'host=10.10.11.1 port=5432 user=replicator
> password=XXXXXX'
> trigger_file = '/var/lib/postgresql/9.1/main/trigger'
> restore_command = 'cp /mnt/p2prddnmdbm_pg_xlog/%f %p'
> root@p2prddnmdbc:/var/lib/postgresql#
>
>
> You don't need primary_conninfo if you only want to restore your
> database. Though that isn't your issue right now.
>
> But with or without a recovery.conf file I can't start the
> database service :
> root@p2prddnmdbc:/var/lib/postgresql# /etc/init.d/postgresql start
> [....] Starting PostgreSQL 9.3 database server: main[....] The
> PostgreSQL server failed to start. Please check the log output:
> 2015-07-14 08:02:59 CEST LOG: database system was interrupted;
> last known up at 2015-07-13 23:33:46 CEST 2015-07-14 08:02:59 CEST
> LOG: invalid checkpoint record 2015-07-14 08:02:59 CEST FATAL:
> could not locate required checkpoint record 2015-07-14 08:02:59
> CEST HINT: If you are not restoring from a backup, try removing
> the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-14
> 08:02:59 CEST LOG: startup process (PID 24617) exited with exit
> co[FAIL2015-07-14 08:02:59 CEST LOG: aborting startup due to
> startup process failure ... failed!
> failed!
>
>
> In your previous email, you had the startup process waiting for a
> file. These logs can't be the good ones.
>
> What you should probably do is tell us exactly what you want to do,
> and state what you do right now, and what logs you get, and what
> processes are on the server. That would help us to help you.
>
> Thanks
> Matthieu
>
> Le 12/07/15 13:46, Guillaume Lelarge a écrit :
>> Hi,
>>
>> 2015-07-12 10:18 GMT+02:00 Matthieu Lejeune
>> <matthieu.lejeune@exxoss.com <mailto:matthieu.lejeune@exxoss.com>>:
>>
>> Hi thank for your reply
>>
>> My target is to give a database for buisness testing query
>> and they are modify the database during the buisness day.
>>
>> Now I got this error if I keep the file backup_label :
>>
>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main#
>> <mailto:root@p2prddnmdbc:/var/lib/postgresql/9.3/main#>
>> /etc/init.d/postgresql start
>> [....] Starting PostgreSQL 9.3 database server: main[....]
>> The PostgreSQL server failed to start. Please check the log
>> output: 2015-07-12 10:12:45 CEST LOG: database system was
>> shut down at 2015-07-12 10:07:10 CEST 2015-07-12 10:12:45
>> CEST LOG: invalid checkpoint record 2015-07-12 10:12:45 CEST
>> FATAL: could not locate required checkpoint record 2015-07-12
>> 10:12:45 CEST HINT: If you are not restoring from a backup,
>> try removing the file
>> "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12
>> 10:12:45 CEST LOG: startup process (PID 28492) exited with
>> exit code 1 2015-07-12 10:12:45 CEST LOG: abo[FAIL startup
>> due to startup process failure ... failed!
>> failed!
>>
>>
>> If I put the recovery.conf the database is waiting for the
>> wal to relaunch the replication.
>>
>> postgres 27817 0.7 0.9 631212 39892 ? S 09:55
>> 0:00 /usr/lib/postgresql/9.3/bin/postgres -D
>> /var/lib/postgresql/9.3/main
>> postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55
>> 0:00 \_ postgres: startup process waiting for
>> 0000000100000178000000B9
>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main#
>> <mailto:root@p2prddnmdbc:/var/lib/postgresql/9.3/main#> su -
>> postgres
>> postgres@p2prddnmdbc:~$ psql energycomm
>> psql: FATAL: the database system is starting up
>>
>> Have you got an idea to stop the replication process and
>> start the database ?
>>
>>
>> What did you put in the recovery.conf file? (hint: standby_mode
>> must be off)
>>
>>
>> Kind regards
>> Matthieu
>>
>> Le 10/07/15 16:46, Keith a écrit :
>>> A recent, relevant post
>>>
>>> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>>>
>>> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge
>>> <guillaume@lelarge.info <mailto:guillaume@lelarge.info>> wrote:
>>>
>>> Hi,
>>>
>>> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune"
>>> <matthieu.lejeune@exxoss.com
>>> <mailto:matthieu.lejeune@exxoss.com>> a écrit :
>>> >
>>> > Hi all,
>>> >
>>> > I have a script for restoring a database every night
>>> to an other postgresql database
>>> >
>>> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
>>> > #/bin/bash
>>> > /etc/init.d/postgresql stop
>>> > mv /var/log/postgresql/postgresql-9.3-main.log
>>> /var/log/postgresql/postgresql-9.3-main.log.old
>>> > cd /var/lib/postgresql/9.3/main
>>> > psql --host=p2prddnmdbm --username=replicator postgres
>>> -c "SELECT pg_start_backup('sync');"
>>> > rsync -av --delete
>>> root@10.10.11.1:/var/lib/postgresql/9.3/main/*
>>> <mailto:root@10.10.11.1:/var/lib/postgresql/9.3/main/*>
>>> /var/lib/postgresql/9.3/main/
>>> > rm backup_label
>>> > chown -R postgres:postgres *
>>> > psql --host=p2prddnmdbm --username=replicator postgres
>>> -c "SELECT pg_stop_backup();"
>>> > /etc/init.d/postgresql start
>>> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
>>> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD
>>> 'XXXX';"
>>> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
>>> > root@p2prddnmdbc:~#
>>> >
>>> >
>>> > But during the day when the user are using the new
>>> database we got error like this :
>>> >
>>> > 2015-06-25 16:20:58 CEST ERROR: could not read block
>>> 257985 in file "base/16386/14064061.1": read only 0 of
>>> 8192 bytes
>>> > 2015-06-22 15:21:11 CEST ERROR: could not read block
>>> 256801 in file "base/16386/14064061.1": read only 0 of
>>> 8192 bytes
>>> >
>>> > I have check the : filesystem on the vm, on the HW SAN,...
>>> >
>>> > Any idea to fix this problem?
>>>
>>> Sure. Don't remove the backup_label file, and add the
>>> recovery.conf file.
>>>
>>> --
>>> Guillaume
>>>
>>>
>>
>>
>>
>>
>>
>> --
>> Guillaume.
>> http://blog.guillaume.lelarge.info
>> http://www.dalibo.com
>
>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-16 18:11 Guillaume Lelarge <guillaume@lelarge.info>
parent: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
0 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2015-07-16 18:11 UTC (permalink / raw)
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
2015-07-14 12:32 GMT+02:00 Matthieu Lejeune <matthieu.lejeune@exxoss.com>:
> Hi,
>
> The master server : 10.10.11.1 is p2prddnmdbm
> I got a real time slave with the recovery process : p2prddnmdbs
>
> I have a third copy with a H-24 data : p2prddnmdbc
> On this server I need to have the copy of the master at 23H00 every day.
> So I'm using the script send before.
> During the day the buisness modify the database on server p2prddnmdbc and
> that's why I use a rsync --delete every night to get a clean copy of the
> prod server.
>
> So what's the best way to do the restore ?
>
>
There were two issues on your script: it removed the backup_label file and
didn't create the recovery.conf file. Did you fix this? if yes, can you
send back your script? Thanks.
Thanks
>
> Matthieu
>
>
> Le 14/07/15 08:50, Guillaume Lelarge a écrit :
>
> 2015-07-14 8:09 GMT+02:00 Matthieu Lejeune <matthieu.lejeune@exxoss.com>:
>
>> Hi,
>>
>> I had no recovery.conf on this server because I launch my replication
>> every night I need a H-24 copy database.
>>
>>
> On your first email, you said: "I have a script for restoring a database
> every night to an other postgresql database". A restore is not replication,
> even it's a PITR restore.
>
> Still on your first email, you execute pg_start_backup() and
> pg_stop_backup() on p2prddnmdbm, and the rsync on 10.10.11.1. I suppose
> p2prddnmdbm and 10.10.11.1 are the same server?
>
> This is my recovery.conf
>> root@p2prddnmdbc:/var/lib/postgresql# cat recovery.conf
>> standby_mode = 'off'
>> primary_conninfo = 'host=10.10.11.1 port=5432 user=replicator
>> password=XXXXXX'
>> trigger_file = '/var/lib/postgresql/9.1/main/trigger'
>> restore_command = 'cp /mnt/p2prddnmdbm_pg_xlog/%f %p'
>> root@p2prddnmdbc:/var/lib/postgresql#
>>
>>
> You don't need primary_conninfo if you only want to restore your
> database. Though that isn't your issue right now.
>
>
>> But with or without a recovery.conf file I can't start the database
>> service :
>> root@p2prddnmdbc:/var/lib/postgresql# /etc/init.d/postgresql start
>> [....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL
>> server failed to start. Please check the log output: 2015-07-14 08:02:59
>> CEST LOG: database system was interrupted; last known up at 2015-07-13
>> 23:33:46 CEST 2015-07-14 08:02:59 CEST LOG: invalid checkpoint record
>> 2015-07-14 08:02:59 CEST FATAL: could not locate required checkpoint record
>> 2015-07-14 08:02:59 CEST HINT: If you are not restoring from a backup, try
>> removing the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-14
>> 08:02:59 CEST LOG: startup process (PID 24617) exited with exit
>> co[FAIL2015-07-14 08:02:59 CEST LOG: aborting startup due to startup
>> process failure ... failed!
>> failed!
>>
>>
> In your previous email, you had the startup process waiting for a file.
> These logs can't be the good ones.
>
> What you should probably do is tell us exactly what you want to do, and
> state what you do right now, and what logs you get, and what processes are
> on the server. That would help us to help you.
>
> Thanks
>> Matthieu
>>
>> Le 12/07/15 13:46, Guillaume Lelarge a écrit :
>>
>> Hi,
>>
>> 2015-07-12 10:18 GMT+02:00 Matthieu Lejeune <
>> <matthieu.lejeune@exxoss.com>matthieu.lejeune@exxoss.com>:
>>
>>> Hi thank for your reply
>>>
>>> My target is to give a database for buisness testing query and they are
>>> modify the database during the buisness day.
>>>
>>> Now I got this error if I keep the file backup_label :
>>>
>>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql
>>> start
>>> [....] Starting PostgreSQL 9.3 database server: main[....] The
>>> PostgreSQL server failed to start. Please check the log output: 2015-07-12
>>> 10:12:45 CEST LOG: database system was shut down at 2015-07-12 10:07:10
>>> CEST 2015-07-12 10:12:45 CEST LOG: invalid checkpoint record 2015-07-12
>>> 10:12:45 CEST FATAL: could not locate required checkpoint record 2015-07-12
>>> 10:12:45 CEST HINT: If you are not restoring from a backup, try removing
>>> the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45
>>> CEST LOG: startup process (PID 28492) exited with exit code 1 2015-07-12
>>> 10:12:45 CEST LOG: abo[FAIL startup due to startup process failure ...
>>> failed!
>>> failed!
>>>
>>>
>>> If I put the recovery.conf the database is waiting for the wal to
>>> relaunch the replication.
>>>
>>> postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00
>>> /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
>>> postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_
>>> postgres: startup process waiting for 0000000100000178000000B9
>>> root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres
>>> postgres@p2prddnmdbc:~$ psql energycomm
>>> psql: FATAL: the database system is starting up
>>>
>>> Have you got an idea to stop the replication process and start the
>>> database ?
>>>
>>>
>> What did you put in the recovery.conf file? (hint: standby_mode must be
>> off)
>>
>>
>>
>>> Kind regards
>>> Matthieu
>>>
>>> Le 10/07/15 16:46, Keith a écrit :
>>>
>>> A recent, relevant post
>>>
>>>
>>> http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html
>>>
>>> On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge <
>>> <guillaume@lelarge.info>guillaume@lelarge.info> wrote:
>>>
>>>> Hi,
>>>>
>>>> Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <
>>>> <matthieu.lejeune@exxoss.com>matthieu.lejeune@exxoss.com> a écrit :
>>>> >
>>>> > Hi all,
>>>> >
>>>> > I have a script for restoring a database every night to an other
>>>> postgresql database
>>>> >
>>>> > root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
>>>> > #/bin/bash
>>>> > /etc/init.d/postgresql stop
>>>> > mv /var/log/postgresql/postgresql-9.3-main.log
>>>> /var/log/postgresql/postgresql-9.3-main.log.old
>>>> > cd /var/lib/postgresql/9.3/main
>>>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>>>> pg_start_backup('sync');"
>>>> > rsync -av --delete <root@10.10.11.1:/var/lib/postgresql/9.3/main/*>
>>>> root@10.10.11.1:/var/lib/postgresql/9.3/main/*
>>>> /var/lib/postgresql/9.3/main/
>>>> > rm backup_label
>>>> > chown -R postgres:postgres *
>>>> > psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT
>>>> pg_stop_backup();"
>>>> > /etc/init.d/postgresql start
>>>> > chmod 777 /var/log/postgresql/postgresql-9.3-main.log
>>>> > psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
>>>> > psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
>>>> > root@p2prddnmdbc:~#
>>>> >
>>>> >
>>>> > But during the day when the user are using the new database we got
>>>> error like this :
>>>> >
>>>> > 2015-06-25 16:20:58 CEST ERROR: could not read block 257985 in file
>>>> "base/16386/14064061.1": read only 0 of 8192 bytes
>>>> > 2015-06-22 15:21:11 CEST ERROR: could not read block 256801 in file
>>>> "base/16386/14064061.1": read only 0 of 8192 bytes
>>>> >
>>>> > I have check the : filesystem on the vm, on the HW SAN,...
>>>> >
>>>> > Any idea to fix this problem?
>>>>
>>>> Sure. Don't remove the backup_label file, and add the recovery.conf
>>>> file.
>>>>
>>>> --
>>>> Guillaume
>>>>
>>>
>>>
>>>
>>>
>>
>>
>> --
>> Guillaume.
>> http://blog.guillaume.lelarge.info
>> http://www.dalibo.com
>>
>>
>>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
>
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Automatic restore corruption problem
@ 2015-07-16 18:39 Kevin Grittner <kgrittn@ymail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2015-07-16 18:39 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; Matthieu Lejeune <matthieu.lejeune@exxoss.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Guillaume Lelarge <guillaume@lelarge.info> wrote:
> There were two issues on your script: it removed the backup_label
> file and didn't create the recovery.conf file.
There were more flaws in the script than that. See:
http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-PITR-RECOVERY
That says, in part:
| Remove any files present in pg_xlog/; these came from the file
| system backup and are therefore probably obsolete rather than
| current.
The point is, if you are unfortunate enough to have recovery use
one of the WAL files copied during the backup, it might not be
complete, since they may have been copied at any point in the
backup -- quite likely before all the WAL records needed for
reaching consistency were added. The archived WAL files will be
complete; set up the recovery.conf file to use them.
Elsewhere on that page it says:
| You might also want to exclude postmaster.pid and
| postmaster.opts, which record information about the running
| postmaster, not about the postmaster which will eventually use
| this backup. (These files can confuse pg_ctl.)
If these files are removed, it is much harder to accidentally bring
up the database with corruption.
> Did you fix this? if yes, can you send back your script? Thanks.
Yeah, if you think you are now following the instructions shown at
the link I show above and are still having problems, please show
all steps in the process -- copy and paste scripts and commands,
and the relevant portions of logs.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2015-07-16 18:39 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-10 12:59 Automatic restore corruption problem Matthieu Lejeune <matthieu.lejeune@exxoss.com>
2015-07-10 13:30 ` michael@sqlexec.com <michael@sqlexec.com>
2015-07-10 14:07 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-07-10 14:46 ` Keith <keith@keithf4.com>
2015-07-12 08:18 ` Matthieu Lejeune <matthieu.lejeune@exxoss.com>
2015-07-12 11:46 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-07-14 06:09 ` Matthieu Lejeune <matthieu.lejeune@exxoss.com>
2015-07-14 06:50 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-07-14 10:32 ` Matthieu Lejeune <matthieu.lejeune@exxoss.com>
2015-07-16 18:11 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-07-16 18:39 ` Kevin Grittner <kgrittn@ymail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox