pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg v. 8.4.5 misses objects and data after restoring from backup using wal
16+ messages / 5 participants
[nested] [flat]
* pg v. 8.4.5 misses objects and data after restoring from backup using wal
@ 2010-12-30 11:03 Imre Oolberg <imre@auul.pri.ee>
2010-12-30 15:10 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 10:59 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
0 siblings, 2 replies; 16+ messages in thread
From: Imre Oolberg @ 2010-12-30 11:03 UTC (permalink / raw)
To: pgsql-admin
Hi!
I did my usual test restoration from backup which leaded me to suspect
that some data is missing although database starts up. I did some more
testing to reproduce the problem as generally as i could and i wish to
present it here and hope that somebody could comment on this. It may
well be my bad as i am doing some systematic error or something else.
I use debian lenny amd64 with postgresql from backports which is
# dpkg -l postgre\* | grep ii
ii postgresql-8.4 8.4.5-2~bpo50+1
object-relational SQL database, version 8.4
ii postgresql-client-8.4 8.4.5-2~bpo50+1
front-end programs for PostgreSQL 8.4
ii postgresql-client-common 111~bpo50+1
manager for multiple PostgreSQL client versi
ii postgresql-common 111~bpo50+1
PostgreSQL database-cluster manager
ii postgresql-contrib-8.4 8.4.5-2~bpo50+1
additional facilities for PostgreSQL
ii postgresql-plperl-8.4 8.4.5-2~bpo50+1
PL/Perl procedural language for PostgreSQL 8
ii postgresql-server-dev-8.4 8.4.5-2~bpo50+1
development files for PostgreSQL 8.4 server-
In addition i have compiled and installed pljava, plproxy and skytools
libraries but in this test i do not use them, i.e. i guess it doest
really matter that the are in /usr/lib/postgresql/8.4/lib directory
since no languages/functions are defined to use them.
PostgreSQL config is default except wal logging is turned on.
My procedure consists of the following steps
1. start up database
2. created schema test1 and into it table test like
CREATE TABLE test1.test (
test_id serial NOT NULL,
pilt bytea,
CONSTRAINT test_id_pkey PRIMARY KEY (test_id)
)
and started inserting data with this script
import psycopg2, cPickle
conn = psycopg2.connect("dbname=test host=127.0.0.1 user=postgres
password=xxx port=5432")
cur = conn.cursor()
f = open('/boot/initrd.img-2.6.26-2-amd64', 'rb')
binary = f.read()
sql = "insert into test1.test (pilt) values (%s)";
cur.execute(sql, (psycopg2.Binary(binary),))
cur.close()
conn.commit()
conn.close()
like
$ for i in `seq 1 200`; do echo $i; python pi1.py; sleep 10; done
3. issued pg_start_backup('test') while script is still running
4. created schemas and tables test2.test and test3.test and started
insterting data similarly
5. while three scripts are running issued rsync -avH --bwlimit=1024
root@localhost:/var/lib/postgresql/ /mnt/backup/
6. while rsync is running i created schemas and tables test4.test and
test5.test and started insterting data similarly
7. when scripts and rsync have been completed i said pg_stop_backup
8. created schema and table test6.test and started insterting data similarly
When everything was finished i had approx 5g used as data cluster
filesystem and about 400 wal logs by 16 MB each.
Restore went like this
1. mounted /mnt/backup/ under /var/lib/postgresql
2. removed backup_label
3. created recovery.conf with a line
restore_command = 'cp /data/backup/postgresql/archive-logs/%f %p'
4. emptied pg_xlog directory while having directory itself
5. started postgresql and it finishes accepting connections
And now observations
1. while i have 400 wal log files during recovery it used only about
last 20 of them
2. while original working postgresql data cluster filesystem's used size
was about 5 gb after restore used space is 2.6 gb
3. looking thru tables, most of them have quarter of half less entries
than was actually insterted and is present in original database
4. some tables are missing (test4.test) and some give errors like
(test5.test) when i say select count(*) from test5.test;
2010-12-30 12:41:23 EET ERROR: catalog is missing 2 attribute(s) for
relid 16586 at character 22
2010-12-30 12:41:23 EET STATEMENT: select count(*) from test5.test;
tried it also with compiled version of 8.4.5 but i chose to use lenny +
backports because it may be this way more reproducable that my compiled
instance. Actually my data size is about 160 gb, and i tested also with
sizes about 1g and 4 tables but successful reproduction seems to need
database size in filesystem around 5 g and 6 tables.
I undestand that this report is not very precise, i.e. i have not fixed
how much data was interted into database at the moment rsyncing started
but i hope it has some useful points and someone could help me further,
i am also ready to do some more testing if it is helpful.
Imre
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2010-12-30 11:03 pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2010-12-30 15:10 ` Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 16+ messages in thread
From: Scott Ribe @ 2010-12-30 15:10 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; +Cc: pgsql-admin
On Dec 30, 2010, at 4:03 AM, Imre Oolberg wrote:
> 3. issued pg_start_backup('test') while script is still running
Well, yeah. Your procedure is going to get you a consistent snapshot of the state of the database when you issued that command.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2010-12-30 11:03 pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2011-01-03 10:59 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 17:36 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 16+ messages in thread
From: Imre Oolberg @ 2011-01-03 10:59 UTC (permalink / raw)
To: pgsql-admin
Hi and Happy New Year!
Unfortunately i still have the same problem, in the mean time i tried
out with different versions and still get same results. Experimenting on
Debian Lenny v 5.0 with v. compiled v. 9.0.2 where default conf is
changed only in this
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /data/backup/postgresql/archive-logs/%f &&
cp %p /data/backup/postgresql/archive-logs'
and having as a starting point of the recovery
1. data cluster file sytem copied with rsync between pg_start_backup and
pg_stop backup
2. wal logs archived starting earier that pg_start_backup and continuing
after pg_stop_backup
I guess that my problem is probably that although my .backup file says
000000010000000000000009.00000020.backup
START WAL LOCATION: 0/9000020 (file 000000010000000000000009)
STOP WAL LOCATION: 1/6325B2E0 (file 000000010000000100000063)
CHECKPOINT LOCATION: 0/A2C4908
START TIME: 2011-01-03 11:32:17 EET
LABEL: test
STOP TIME: 2011-01-03 12:18:27 EET
looking at the pg_ctl.log it starts reading wal logs beginning with
000000010000000100000063 and not with 000000010000000000000009
LOG: database system was interrupted; last known up at 2011-01-03
11:58:56 EET
LOG: creating missing WAL directory "pg_xlog/archive_status"
LOG: starting archive recovery
LOG: restored log file "000000010000000100000063" from archive
LOG: consistent recovery state reached at 1/6325B2B8
LOG: redo starts at 1/6325B2B8
LOG: restored log file "000000010000000100000064" from archive
LOG: restored log file "000000010000000100000065" from archive
LOG: restored log file "000000010000000100000066" from archive
...
LOG: restored log file "000000010000000100000081" from archive
cp: cannot stat
`/data/backup/postgresql/archive-logs/000000010000000100000082': No such
file or directory
LOG: could not open file "pg_xlog/000000010000000100000082" (log file
1, segment 130): No such file or directory
LOG: redo done at 1/81530E18
LOG: last completed transaction was at log time 2011-01-03
12:20:31.917695+02
LOG: restored log file "000000010000000100000081" from archive
cp: cannot stat `/data/backup/postgresql/archive-logs/00000002.history':
No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/data/backup/postgresql/archive-logs/00000001.history':
No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
I tried to follow a pitr procedure as per
http://www.postgresql.org/docs/9.0/interactive/continuous-archiving.html
and i would be very greatful if somebody comments this and points me to
the right direction.
Best regards,
Imre
On 12/30/10 13:03, Imre Oolberg wrote:
> Hi!
>
> I did my usual test restoration from backup which leaded me to suspect
> that some data is missing although database starts up. I did some more
> testing to reproduce the problem as generally as i could and i wish to
> present it here and hope that somebody could comment on this. It may
> well be my bad as i am doing some systematic error or something else.
>
> I use debian lenny amd64 with postgresql from backports which is
>
> # dpkg -l postgre\* | grep ii
> ii postgresql-8.4 8.4.5-2~bpo50+1
> object-relational SQL database, version 8.4
> ii postgresql-client-8.4 8.4.5-2~bpo50+1 front-end
> programs for PostgreSQL 8.4
> ii postgresql-client-common 111~bpo50+1 manager for
> multiple PostgreSQL client versi
> ii postgresql-common 111~bpo50+1 PostgreSQL
> database-cluster manager
> ii postgresql-contrib-8.4 8.4.5-2~bpo50+1 additional
> facilities for PostgreSQL
> ii postgresql-plperl-8.4 8.4.5-2~bpo50+1 PL/Perl
> procedural language for PostgreSQL 8
> ii postgresql-server-dev-8.4 8.4.5-2~bpo50+1 development
> files for PostgreSQL 8.4 server-
>
> In addition i have compiled and installed pljava, plproxy and skytools
> libraries but in this test i do not use them, i.e. i guess it doest
> really matter that the are in /usr/lib/postgresql/8.4/lib directory
> since no languages/functions are defined to use them.
>
> PostgreSQL config is default except wal logging is turned on.
>
> My procedure consists of the following steps
>
> 1. start up database
> 2. created schema test1 and into it table test like
>
> CREATE TABLE test1.test (
> test_id serial NOT NULL,
> pilt bytea,
> CONSTRAINT test_id_pkey PRIMARY KEY (test_id)
> )
>
> and started inserting data with this script
>
> import psycopg2, cPickle
> conn = psycopg2.connect("dbname=test host=127.0.0.1 user=postgres
> password=xxx port=5432")
> cur = conn.cursor()
>
> f = open('/boot/initrd.img-2.6.26-2-amd64', 'rb')
> binary = f.read()
> sql = "insert into test1.test (pilt) values (%s)";
> cur.execute(sql, (psycopg2.Binary(binary),))
> cur.close()
> conn.commit()
> conn.close()
>
> like
>
> $ for i in `seq 1 200`; do echo $i; python pi1.py; sleep 10; done
>
> 3. issued pg_start_backup('test') while script is still running
> 4. created schemas and tables test2.test and test3.test and started
> insterting data similarly
> 5. while three scripts are running issued rsync -avH --bwlimit=1024
> root@localhost:/var/lib/postgresql/ /mnt/backup/
> 6. while rsync is running i created schemas and tables test4.test and
> test5.test and started insterting data similarly
> 7. when scripts and rsync have been completed i said pg_stop_backup
> 8. created schema and table test6.test and started insterting data
> similarly
>
> When everything was finished i had approx 5g used as data cluster
> filesystem and about 400 wal logs by 16 MB each.
>
> Restore went like this
>
> 1. mounted /mnt/backup/ under /var/lib/postgresql
> 2. removed backup_label
> 3. created recovery.conf with a line
>
> restore_command = 'cp /data/backup/postgresql/archive-logs/%f %p'
>
> 4. emptied pg_xlog directory while having directory itself
> 5. started postgresql and it finishes accepting connections
>
> And now observations
>
> 1. while i have 400 wal log files during recovery it used only about
> last 20 of them
> 2. while original working postgresql data cluster filesystem's used
> size was about 5 gb after restore used space is 2.6 gb
> 3. looking thru tables, most of them have quarter of half less entries
> than was actually insterted and is present in original database
> 4. some tables are missing (test4.test) and some give errors like
> (test5.test) when i say select count(*) from test5.test;
>
> 2010-12-30 12:41:23 EET ERROR: catalog is missing 2 attribute(s) for
> relid 16586 at character 22
> 2010-12-30 12:41:23 EET STATEMENT: select count(*) from test5.test;
>
> tried it also with compiled version of 8.4.5 but i chose to use lenny
> + backports because it may be this way more reproducable that my
> compiled instance. Actually my data size is about 160 gb, and i tested
> also with sizes about 1g and 4 tables but successful reproduction
> seems to need database size in filesystem around 5 g and 6 tables.
>
> I undestand that this report is not very precise, i.e. i have not
> fixed how much data was interted into database at the moment rsyncing
> started but i hope it has some useful points and someone could help me
> further, i am also ready to do some more testing if it is helpful.
>
>
> Imre
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2010-12-30 11:03 pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 10:59 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2011-01-03 17:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-03 18:37 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2011-01-03 17:36 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; pgsql-admin
Imre Oolberg <imre@auul.pri.ee> wrote:
> archive_command = 'test ! -f
> /data/backup/postgresql/archive-logs/%f &&
> cp %p /data/backup/postgresql/archive-logs'
I suppose that will work, but why not specify %f in the copy target?
> I guess that my problem is probably that although my .backup file
> says
>
> 000000010000000000000009.00000020.backup
> START WAL LOCATION: 0/9000020 (file 000000010000000000000009)
> STOP WAL LOCATION: 1/6325B2E0 (file 000000010000000100000063)
> CHECKPOINT LOCATION: 0/A2C4908
> START TIME: 2011-01-03 11:32:17 EET
> LABEL: test
> STOP TIME: 2011-01-03 12:18:27 EET
>
> looking at the pg_ctl.log it starts reading wal logs beginning
> with 000000010000000100000063 and not with
> 000000010000000000000009
What did you have in your recovery.conf file?
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2010-12-30 11:03 pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 10:59 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 17:36 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-01-03 18:37 ` Imre Oolberg <imre@auul.pri.ee>
0 siblings, 0 replies; 16+ messages in thread
From: Imre Oolberg @ 2011-01-03 18:37 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
On 01/03/11 19:36, Kevin Grittner wrote:
> Imre Oolberg<imre@auul.pri.ee> wrote:
>
>
>> archive_command = 'test ! -f
>> /data/backup/postgresql/archive-logs/%f&&
>> cp %p /data/backup/postgresql/archive-logs'
>>
>
> I suppose that will work, but why not specify %f in the copy target?
>
>
Yep, that works as it is since wal files are created althought to use %f
should not hurt either.
>> I guess that my problem is probably that although my .backup file
>> says
>>
>> 000000010000000000000009.00000020.backup
>> START WAL LOCATION: 0/9000020 (file 000000010000000000000009)
>> STOP WAL LOCATION: 1/6325B2E0 (file 000000010000000100000063)
>> CHECKPOINT LOCATION: 0/A2C4908
>> START TIME: 2011-01-03 11:32:17 EET
>> LABEL: test
>> STOP TIME: 2011-01-03 12:18:27 EET
>>
>> looking at the pg_ctl.log it starts reading wal logs beginning
>> with 000000010000000100000063 and not with
>> 000000010000000000000009
>>
>
> What did you have in your recovery.conf file?
>
I have there exactly one line
restore_command = 'cp /data/backup/postgresql/archive-logs/%f %p'
and with it i expect to restore as much data as i have wal files
archived. The most strange thing is that it uses archived wal files
which are generated after pg_stop_backup but skips all others.
Imre
>
> -Kevin
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
@ 2011-01-03 16:36 pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
0 siblings, 1 reply; 16+ messages in thread
From: pasman pasmański @ 2011-01-03 16:36 UTC (permalink / raw)
To: pgsql-admin
The problem exists when you do a base backup with pg_backup?
Maybe rsync skip some data ?
------------
pasman
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
@ 2011-01-03 18:22 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 16+ messages in thread
From: Imre Oolberg @ 2011-01-03 18:22 UTC (permalink / raw)
To: pgsql-admin
Hi!
Thanks for the answer.
On 01/03/11 18:36, pasman pasmański wrote:
> The problem exists when you do a base backup with pg_backup?
> Maybe rsync skip some data ?
>
>
Yes, problem exists using pg_start/stop_backup as i discribed.
I am sure that 'rsync -avH ...' and 'tar cvf ...' will skip at least
additions made to the filesystem tree after they are started. In this
case rsync says nothing and tar says
tar: directory-name: file changed as we read it
But
http://www.postgresql.org/docs/9.0/interactive/continuous-archiving.html
suggests to use tar on rsync and i guess that PostgreSQL recovery with
wal files takes care of these inconsistencies that are created during
copying filesystem, right?
Imre
> ------------
> pasman
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2011-01-03 18:31 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:38 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 16+ messages in thread
From: Scott Ribe @ 2011-01-03 18:31 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; +Cc: pgsql-admin
On Jan 3, 2011, at 11:22 AM, Imre Oolberg wrote:
> But http://www.postgresql.org/docs/9.0/interactive/continuous-archiving.html suggests to use tar on rsync and i guess that PostgreSQL recovery with wal files takes care of these inconsistencies that are created during copying filesystem, right?
Yes, but the database is recovered to the consistent state as of the pg_start_backup command, as I pointed out to you before. Results of transactions that commit after the pg_start_backup command will not be in the backed up database.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
@ 2011-01-03 18:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2011-01-03 18:38 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> Yes, but the database is recovered to the consistent state as of
> the pg_start_backup command, as I pointed out to you before.
> Results of transactions that commit after the pg_start_backup
> command will not be in the backed up database.
I doubt you. The transactions between pg_start_backup and
pg_stop_backup must all be applied to get to a consistent state.
That means you will be *at least* to the point of the stop. If
there are more WAL files available to apply, and you don't tell it
otherwise, it will keep going *past* the stop point until the
attempt to get a WAL file fails.
It sounds to me more like the OP isn't following all the
instructions for a PITR recovery, and is instead falling into crash
recovery. That won't work on a copy made while a database was being
modified.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
@ 2011-01-03 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2011-01-03 18:42 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Imre Oolberg <imre@auul.pri.ee>; pgsql-admin
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> On Jan 3, 2011, at 11:22 AM, Imre Oolberg wrote:
>> But http://www.postgresql.org/docs/9.0/interactive/continuous-archiving.html suggests to use tar on rsync and i guess that PostgreSQL recovery with wal files takes care of these inconsistencies that are created during copying filesystem, right?
> Yes, but the database is recovered to the consistent state as of the pg_start_backup command, as I pointed out to you before. Results of transactions that commit after the pg_start_backup command will not be in the backed up database.
That's either incorrect or poorly worded. The only way to get a
consistent, usable database is to replay all the WAL that was generated
between pg_start_backup and pg_stop_backup. That will fix up whatever
inconsistencies exist in the base backup fileset. Once you've done
that, you do have the results of transactions that committed after
pg_start_backup (and up to pg_stop_backup). If you haven't done that,
what you have is an inconsistent pile of rather arbitrary bits. The
base backup by itself (without the concurrently-created WAL) is
*useless*.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-03 18:49 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Ribe @ 2011-01-03 18:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Imre Oolberg <imre@auul.pri.ee>; pgsql-admin
On Jan 3, 2011, at 11:42 AM, Tom Lane wrote:
> That's either incorrect or poorly worded.
Let's go with poorly worded then. (Actually I don't do this, I use streaming replication, where I don't have to copy the WAL files because the standby picks up the ones that are needed.)
Unless I misread his earlier post, he is starting the backup, copying files, stopping the backup, and expecting those transactions (between start & stop) to be applied, even though files changed during that time (including WAL files) have not all been copied. Again, unless I misread his earlier post, he is *not* copying WAL that was generated between start & stop.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
@ 2011-01-03 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:15 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2011-01-03 18:56 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Imre Oolberg <imre@auul.pri.ee>; pgsql-admin
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Unless I misread his earlier post, he is starting the backup, copying files, stopping the backup, and expecting those transactions (between start & stop) to be applied, even though files changed during that time (including WAL files) have not all been copied. Again, unless I misread his earlier post, he is *not* copying WAL that was generated between start & stop.
If that's the case, it just plain won't work. You can either shut down
the postmaster and copy a static set of files, or you can apply WAL to a
base backup that was taken from a live database. There is no way to get
consistent results from a backup of a live database without applying all
the WAL that was generated while the backup was being taken.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-03 20:15 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 20:24 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:37 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 16+ messages in thread
From: Imre Oolberg @ 2011-01-03 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Hi!
On 01/03/11 20:56, Tom Lane wrote:
> Scott Ribe<scott_ribe@elevated-dev.com> writes:
>
>> Unless I misread his earlier post, he is starting the backup, copying files, stopping the backup, and expecting those transactions (between start& stop) to be applied, even though files changed during that time (including WAL files) have not all been copied. Again, unless I misread his earlier post, he is *not* copying WAL that was generated between start& stop.
>>
> If that's the case, it just plain won't work. You can either shut down
> the postmaster and copy a static set of files, or you can apply WAL to a
> base backup that was taken from a live database. There is no way to get
> consistent results from a backup of a live database without applying all
> the WAL that was generated while the backup was being taken.
>
> regards, tom lane
>
First of all, thanks for your time dealing with my situation. I must
stress that i have wal arhived starting from before issuing
pg_start_backup, wal archives generated before pg_start/stop_backup and
also some wal archive files generated after pg_stop_backup (and
permissions are ok). But for some reason my replay starts exactly where
.backup file says 'stop wal location', referring to my earlier post
STOP WAL LOCATION: 1/6325B2E0 (file 000000010000000100000063)
I am starting to think more in the direction what Kevin Grittner
suggested that i miss something in my procedure and instead following so
to say recovery path i am doing something else which strangely ends up
with working database process but actually misses some data. I really
tried to write down all the steps i take in my first mail, maybe you
could say something based on that or i could do some additional checking
(with pg_controldata or the like) before starting database in wal-replay
mode. In the meantime a try to read thru manual once more and try out
streaming replication, hope it advances my general understanding of the
process and helps in solving the current problem.
Best regards,
Imre
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:15 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2011-01-03 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 21:06 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2011-01-03 20:24 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Imre Oolberg <imre@auul.pri.ee> writes:
> First of all, thanks for your time dealing with my situation. I must
> stress that i have wal arhived starting from before issuing
> pg_start_backup, wal archives generated before pg_start/stop_backup and
> also some wal archive files generated after pg_stop_backup (and
> permissions are ok). But for some reason my replay starts exactly where
> .backup file says 'stop wal location', referring to my earlier post
> STOP WAL LOCATION: 1/6325B2E0 (file 000000010000000100000063)
> I am starting to think more in the direction what Kevin Grittner
> suggested that i miss something in my procedure and instead following so
> to say recovery path i am doing something else which strangely ends up
> with working database process but actually misses some data.
I wonder whether you're failing to copy the backup_label file as part of
the base backup. The presence of that file is what tells the slave
postmaster where it has to start recovering from.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:15 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 20:24 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-03 21:06 ` Imre Oolberg <imre@auul.pri.ee>
0 siblings, 0 replies; 16+ messages in thread
From: Imre Oolberg @ 2011-01-03 21:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Hi!
On 01/03/11 22:24, Tom Lane wrote:
> I wonder whether you're failing to copy the backup_label file as part of
> the base backup. The presence of that file is what tells the slave
> postmaster where it has to start recovering from.
>
> regards, tom lane
>
Thank you so much for your attention, i really need to learn reading
postgresql.org manuals, probably already a long time ago i learned from
somewhere that i need to delete backup_label file before starting up
wal-recoverying database and i did, as presented in my first mail
Restore went like this
1. mounted /mnt/backup/ under /var/lib/postgresql
2. removed backup_label
3. created recovery.conf with a line
restore_command = 'cp /data/backup/postgresql/archive-logs/%f %p'
4. emptied pg_xlog directory while having directory itself
5. started postgresql and it finishes accepting connections
Now everything works, in the process backup_label gets renamed
backup_label.old, thanks again for your attention! Still lucky it was
mistake in the recovery procedure having my backups made so far still
usable :)
Imre
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:42 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:15 ` Re: Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
@ 2011-01-03 20:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2011-01-03 20:37 UTC (permalink / raw)
To: Imre Oolberg <imre@auul.pri.ee>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Imre Oolberg <imre@auul.pri.ee> wrote:
> I am starting to think more in the direction what Kevin Grittner
> suggested that i miss something in my procedure and instead
> following so to say recovery path i am doing something else which
> strangely ends up with working database process but actually
> misses some data.
Did you keep the pg_xlog/archive_status directory and its *.backup
files?
It sounds like maybe it's not finding the recovery.conf file. Are
the permissions right? Do you have spaces or other special
characters in the directory path to your cluster data directory? (I
notice you didn't use quoting around the path.)
Did you delete (or avoid copying) the postmaster.pid file?
Making sure to start with an empty log file and/or directory
(depending on how you log), what shows up in the log on your
recovery attempt?
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2011-01-03 21:06 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-30 11:03 pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2010-12-30 15:10 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 10:59 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 17:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-03 18:37 ` Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal Imre Oolberg <imre@auul.pri.ee>
2011-01-03 16:36 Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal pasman pasmański <pasman.p@gmail.com>
2011-01-03 18:22 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 18:31 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-03 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 18:49 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-03 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 20:15 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 21:06 ` Imre Oolberg <imre@auul.pri.ee>
2011-01-03 20:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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