pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to recover database back from /data folder
8+ messages / 4 participants
[nested] [flat]
* how to recover database back from /data folder
@ 2006-12-10 05:47 yogesh@banasdairy.coop
0 siblings, 1 reply; 8+ messages in thread
From: yogesh@banasdairy.coop @ 2006-12-10 05:47 UTC (permalink / raw)
To: pgsql-admin
hi to all
last time
my redhat 9 is corrupt so at that time itake backup of my postgresql database
/data folder
so after two days i restall the redhat 9
than i try to copy old /data folder over newly /data folder
but it does not start postgresql service
so please any one give me solution
--
This message has been scanned for viruses and
dangerous content by BanasDairy Mailserver , and is
believed to be clean.For any query contact at
system@banasdairy.coop
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder
@ 2006-12-10 08:10 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: yogesh@banasdairy.coop
0 siblings, 1 reply; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-10 08:10 UTC (permalink / raw)
To: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; +Cc: pgsql-admin
On 12/10/06, yogesh@banasdairy.coop <yogesh@banasdairy.coop> wrote:
> hi to all
> last time
> my redhat 9 is corrupt so at that time itake backup of my postgresql database
> /data folder
Yogesh,
Backup of postgresql is done by a specific process.
Simply copying /data ( PGDATA) is not the way (generally)
have you been doing pg_dump or pg_dumpall for your database
regularly ?
http://www.postgresql.org/docs/8.0/static/backup-file.html
22.2. File system level backup
An alternative backup strategy is to directly copy the files that
PostgreSQL uses to store the data in the database. In Section 16.2 it
is explained where these files are located, but you have probably
found them already if you are interested in this method. You can use
whatever method you prefer for doing usual file system backups, for
example
tar -cf backup.tar /usr/local/pgsql/data
There are two restrictions, however, which make this method
impractical, or at least inferior to the pg_dump method:
1.
The database server must be shut down in order to get a usable
backup. Half-way measures such as disallowing all connections will not
work (mainly because tar and similar tools do not take an atomic
snapshot of the state of the file system at a point in time).
Information about stopping the server can be found in Section 16.6.
Needless to say that you also need to shut down the server before
restoring the data.
2.
If you have dug into the details of the file system layout of
the database, you may be tempted to try to back up or restore only
certain individual tables or databases from their respective files or
directories. This will not work because the information contained in
these files contains only half the truth. The other half is in the
commit log files pg_clog/*, which contain the commit status of all
transactions. A table file is only usable with this information. Of
course it is also impossible to restore only a table and the
associated pg_clog data because that would render all other tables in
the database cluster useless. So file system backups only work for
complete restoration of an entire database cluster.
>
> so after two days i restall the redhat 9
> than i try to copy old /data folder over newly /data folder
>
> but it does not start postgresql service
> so please any one give me solution
>
> --
> This message has been scanned for viruses and
> dangerous content by BanasDairy Mailserver , and is
> believed to be clean.For any query contact at
> system@banasdairy.coop
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder
@ 2006-12-10 08:40 Shoaib Mir <shoaibmir@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Shoaib Mir @ 2006-12-10 08:40 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; pgsql-admin
Is it the same version of PostgreSQL you have installed again? more if you
can tell the exact error on the starting database server, that might help as
well... also make the permission on the data folder are all good as it were
before
Thanks,
----------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 12/10/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
>
> On 12/10/06, yogesh@banasdairy.coop <yogesh@banasdairy.coop> wrote:
> > hi to all
> > last time
> > my redhat 9 is corrupt so at that time itake backup of my postgresql
> database
> > /data folder
>
> Yogesh,
>
> Backup of postgresql is done by a specific process.
> Simply copying /data ( PGDATA) is not the way (generally)
> have you been doing pg_dump or pg_dumpall for your database
> regularly ?
>
> http://www.postgresql.org/docs/8.0/static/backup-file.html
>
> 22.2. File system level backup
>
> An alternative backup strategy is to directly copy the files that
> PostgreSQL uses to store the data in the database. In Section 16.2 it
> is explained where these files are located, but you have probably
> found them already if you are interested in this method. You can use
> whatever method you prefer for doing usual file system backups, for
> example
>
> tar -cf backup.tar /usr/local/pgsql/data
>
> There are two restrictions, however, which make this method
> impractical, or at least inferior to the pg_dump method:
>
> 1.
>
> The database server must be shut down in order to get a usable
> backup. Half-way measures such as disallowing all connections will not
> work (mainly because tar and similar tools do not take an atomic
> snapshot of the state of the file system at a point in time).
> Information about stopping the server can be found in Section 16.6.
> Needless to say that you also need to shut down the server before
> restoring the data.
> 2.
>
> If you have dug into the details of the file system layout of
> the database, you may be tempted to try to back up or restore only
> certain individual tables or databases from their respective files or
> directories. This will not work because the information contained in
> these files contains only half the truth. The other half is in the
> commit log files pg_clog/*, which contain the commit status of all
> transactions. A table file is only usable with this information. Of
> course it is also impossible to restore only a table and the
> associated pg_clog data because that would render all other tables in
> the database cluster useless. So file system backups only work for
> complete restoration of an entire database cluster.
>
>
>
> >
> > so after two days i restall the redhat 9
> > than i try to copy old /data folder over newly /data folder
> >
> > but it does not start postgresql service
> > so please any one give me solution
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by BanasDairy Mailserver , and is
> > believed to be clean.For any query contact at
> > system@banasdairy.coop
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder [ Possibly Recovered ]
@ 2006-12-11 14:07 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-11 14:07 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; pgsql-admin
Hi List ,
I restored the filesystem backup tar and ran postgres 7.3.8 on the data folder.
there was no problem at all.
DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postgres" using argv[0]
DEBUG: invoking IpcMemoryCreate(size=1466368)
DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postmaster" using argv[0]
LOG: database system was shut down at 2006-12-04 12:30:21 IST
LOG: checkpoint record is at 0/39DF3C2C
LOG: redo record is at 0/39DF3C2C; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 2006905; next oid: 15212626
LOG: database system is ready
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes
I think the above messages support the fact that the database was shutdown
properly before the filesystem level backup.
Can anyone kindly confirm it ?
I listed the tables and did some selects the data seems to be there.
I think postgres was not starting for some peripheral issues
not because that data folder was corrupted.
BTW
when i first started postmaster it gave an error related to
too liberal permissions. Probably the original poster was doing
the same mistake.
Regds
mallah.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder [ Possibly
@ 2006-12-11 14:11 Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Andy Shellam (Mailing Lists) @ 2006-12-11 14:11 UTC (permalink / raw)
To: pgsql-admin
> I think the above messages support the fact that the database was
> shutdown
> properly before the filesystem level backup.
> Can anyone kindly confirm it ?
"LOG: database system is ready"
Says it all - if it hadn't been, you'd have gotten "LOG: database system
was not shut-down cleanly, recovering" or along those lines.
>
> I listed the tables and did some selects the data seems to be there.
> I think postgres was not starting for some peripheral issues
> not because that data folder was corrupted.
>
> BTW
> when i first started postmaster it gave an error related to
> too liberal permissions. Probably the original poster was doing
> the same mistake.
>
> Regds
> mallah.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> !DSPAM:37,457d667230861609539772!
>
>
--
Andy Shellam
NetServe Support Team
the Mail Network
"an alternative in a standardised world"
p: +44 (0) 121 288 0832/0839
m: +44 (0) 7818 000834
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder [ Possibly Recovered ]
@ 2006-12-11 14:45 Shoaib Mir <shoaibmir@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Shoaib Mir @ 2006-12-11 14:45 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; pgsql-admin
All looks according to the logs to me...
-----------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 12/11/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
>
> Hi List ,
>
> I restored the filesystem backup tar and ran postgres 7.3.8 on the data
> folder.
> there was no problem at all.
>
> DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postgres" using
> argv[0]
> DEBUG: invoking IpcMemoryCreate(size=1466368)
> DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postmaster" using
> argv[0]
> LOG: database system was shut down at 2006-12-04 12:30:21 IST
> LOG: checkpoint record is at 0/39DF3C2C
> LOG: redo record is at 0/39DF3C2C; undo record is at 0/0; shutdown TRUE
> LOG: next transaction id: 2006905; next oid: 15212626
> LOG: database system is ready
> DEBUG: proc_exit(0)
> DEBUG: shmem_exit(0)
> DEBUG: exit(0)
> DEBUG: reaping dead processes
>
> I think the above messages support the fact that the database was shutdown
> properly before the filesystem level backup.
> Can anyone kindly confirm it ?
>
> I listed the tables and did some selects the data seems to be there.
> I think postgres was not starting for some peripheral issues
> not because that data folder was corrupted.
>
> BTW
> when i first started postmaster it gave an error related to
> too liberal permissions. Probably the original poster was doing
> the same mistake.
>
> Regds
> mallah.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder [ Possibly Recovered ]
@ 2006-12-11 14:45 Shoaib Mir <shoaibmir@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Shoaib Mir @ 2006-12-11 14:45 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; pgsql-admin
I meant "all looks *good* according to the logs" :)
-----------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
>
> All looks according to the logs to me...
>
> -----------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 12/11/06, Rajesh Kumar Mallah < mallah.rajesh@gmail.com> wrote:
> >
> > Hi List ,
> >
> > I restored the filesystem backup tar and ran postgres 7.3.8 on the data
> > folder.
> > there was no problem at all.
> >
> > DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postgres" using
> > argv[0]
> > DEBUG: invoking IpcMemoryCreate(size=1466368)
> > DEBUG: FindExec: found "/opt/usr/local/pgsql7.3/bin/postmaster" using
> > argv[0]
> > LOG: database system was shut down at 2006-12-04 12:30:21 IST
> > LOG: checkpoint record is at 0/39DF3C2C
> > LOG: redo record is at 0/39DF3C2C; undo record is at 0/0; shutdown TRUE
> > LOG: next transaction id: 2006905; next oid: 15212626
> > LOG: database system is ready
> > DEBUG: proc_exit(0)
> > DEBUG: shmem_exit(0)
> > DEBUG: exit(0)
> > DEBUG: reaping dead processes
> >
> > I think the above messages support the fact that the database was
> > shutdown
> > properly before the filesystem level backup.
> > Can anyone kindly confirm it ?
> >
> > I listed the tables and did some selects the data seems to be there.
> > I think postgres was not starting for some peripheral issues
> > not because that data folder was corrupted.
> >
> > BTW
> > when i first started postmaster it gave an error related to
> > too liberal permissions. Probably the original poster was doing
> > the same mistake.
> >
> > Regds
> > mallah.
> >
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to recover database back from /data folder [ Recovered ]
@ 2006-12-11 15:35 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-11 15:35 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: yogesh@banasdairy.coop <yogesh@banasdairy.coop>; pgsql-admin; andy.shellam@mailnetwork.co.uk
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
> I meant "all looks *good* according to the logs" :)
thanks everyone.
regds
mallah.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-12-11 15:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-10 05:47 how to recover database back from /data folder yogesh@banasdairy.coop
2006-12-10 08:10 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-10 08:40 ` Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 14:07 Re: how to recover database back from /data folder [ Possibly Recovered ] Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-11 14:11 ` Re: how to recover database back from /data folder [ Possibly Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2006-12-11 14:45 ` Re: how to recover database back from /data folder [ Possibly Recovered ] Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 14:45 ` Re: how to recover database back from /data folder [ Possibly Recovered ] Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 15:35 Re: how to recover database back from /data folder [ Recovered ] Rajesh Kumar Mallah <mallah.rajesh@gmail.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