Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 17:19 -0500, Tom Lane wrote: > This behavior might be all right for an emergency recovery kind of tool, > but I can't see us considering it a supported feature. I agree post-recovery cleanup would be required to bring up a fully safe read-write database. That's one of the reasons my longer term thoughts are towards running transactions immediately after recovery completes, for other uses also. > The larger point though is that I suspect what the OP really is looking > for is "restore just this one database into my existing cluster, without > breaking the other databases that are already in it". There is zero > chance of ever doing that with a WAL-based backup --- transaction ID > inconsistencies would break it, even without considering the contents > of shared catalogs. Agreed. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
Simon Riggs writes: > On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote: >> Simon Riggs writes: > On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: It's not as easy as all that. What will you do with updates to shared catalogs? >> >>> Apply them. >> >> ... which leaves your other databases in inconsistent states. > Which is not a problem if you didn't want to restore them in the first > place. Only for small values of "not a problem". For example, you might have pg_shdepend entries saying that various objects in some other database depend on some role. If you then want to drop the role, you can't; and you can't attach to the other database to get rid of the objects, since it's not there. You'd also still have pg_database entries pointing at the not-there databases. This behavior might be all right for an emergency recovery kind of tool, but I can't see us considering it a supported feature. The larger point though is that I suspect what the OP really is looking for is "restore just this one database into my existing cluster, without breaking the other databases that are already in it". There is zero chance of ever doing that with a WAL-based backup --- transaction ID inconsistencies would break it, even without considering the contents of shared catalogs. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: > >> It's not as easy as all that. What will you do with updates to shared > >> catalogs? > > > Apply them. > > ... which leaves your other databases in inconsistent states. Which is not a problem if you didn't want to restore them in the first place. You might complain that we would need safeguards to protect people from trying to access non-restored databases and then failing to understand why they aren't there. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
Simon Riggs writes: > On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: >> It's not as easy as all that. What will you do with updates to shared >> catalogs? > Apply them. ... which leaves your other databases in inconsistent states. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: > >> samana srikanth wrote: > >>> Can we do a point-in-time restore of a single database out of n > >>> databases??. > >> > >> In principle no. But you could invent workarounds such as recovering to > >> the point where you are happy with your restored one database, and then > >> restore the other n-1 databases from an SQL dump. > > > It is possible, but we just don't currently support it. > > It's not as easy as all that. What will you do with updates to shared > catalogs? Apply them. So: its possible to do shared catalogs plus a subset of other databases. I was assuming that updates to shared catalogs were small overall. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
Simon Riggs writes: > On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: >> samana srikanth wrote: >>> Can we do a point-in-time restore of a single database out of n >>> databases??. >> >> In principle no. But you could invent workarounds such as recovering to >> the point where you are happy with your restored one database, and then >> restore the other n-1 databases from an SQL dump. > It is possible, but we just don't currently support it. It's not as easy as all that. What will you do with updates to shared catalogs? regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: > samana srikanth wrote: > > Can we do a point-in-time restore of a single database out of n > > databases??. > > In principle no. But you could invent workarounds such as recovering to > the point where you are happy with your restored one database, and then > restore the other n-1 databases from an SQL dump. It is possible, but we just don't currently support it. My submission on rmgr plugins would have provided this feature though it was rejected as "not wanted". I have code hooks required to do this, if people want to contact me off-list. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
samana srikanth wrote: Can we do a point-in-time restore of a single database out of n databases??. In principle no. But you could invent workarounds such as recovering to the point where you are happy with your restored one database, and then restore the other n-1 databases from an SQL dump. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgressql backup/restore question
Hi all Can we do a point-in-time restore of a single database out of n databases??. -- I have 5 databases in the postgresql server. I have taken full-backup of the entire data directory (/opt/postgresql/data) and individual dumps also (for safety). then i have taken all the log files till now. Now, the server is crashed. Is there anyway to restore only 1 database out of total 5 database to particular point-in-time. (similar to Mysql) I have individual dumps of each database and all corresponding log files from that time. Can i selectively restore the databases. Can I use dump files + log files to restore the databases. - Please help me in this regards Thanks Srikanth
