Обсуждение: backups and WAL
Hi, first of all thanks for postgresql, it has never given a problem and is performing great. Now my question: my employer wants to do backups every 12 hours, and save the logs evry 2 hours to... Can I simply save the logs (cp pg_xlog/* ...) or do I need something more elaborate? Now in a restore situation ... I restore the database, and then what? put the saved files back in the pg_xlog/ dir, then start postmaster and magically all starts working? Am I correct or completely insane? The documentation is not clear in that aspect... Thanks in advance, Pablo.
pablobarbachano@yahoo.es wrote: > Hi, first of all thanks for postgresql, it has never given a problem > and is performing great. > > Now my question: my employer wants to do backups every 12 hours, and > save the logs evry 2 hours to... Can I simply save the logs > (cp pg_xlog/* ...) or do I need something more elaborate? > > Now in a restore situation ... I restore the database, and then what? > put the saved files back in the pg_xlog/ dir, then start postmaster > and magically all starts working? Am I correct or completely insane? > The documentation is not clear in that aspect... I suppose that you use a pre 8.0 version, if yes take a look at: http://www.postgresql.org/docs/7.4/interactive/backup.html take also a look at PITR available in the future ( near I hope :-) ) 8.0: http://developer.postgresql.org/docs/postgres/backup-online.html Regards Gaetano Mendola
On this issue,
Is a good idea to ln -s the pg_xlog directory onto another place, while the postmaster is running?
I agree with pablo, documentation at this point is not easy to found.
Regards,
> pablobarbachano@yahoo.es wrote:
>
> > Hi, first of all thanks for postgresql, it has never given a problem
> > and is performing great.
> >
> > Now my question: my employer wants to do backups every 12 hours, and
> > save the logs evry 2 hours to... Can I simply save the logs
> > (cp pg_xlog/* ...) or do I need something more elaborate?
> >
> > Now in a restore situation ... I restore the database, and then what?
> > put the saved files back in the pg_xlog/ dir, then start postmaster
> > and magically all starts working? Am I correct or completely insane?
> > The documentation is not clear in that aspect...
>
> I suppose that you use a pre 8.0 version, if yes take a look at:
>
> http://www.postgresql.org/docs/7.4/interactive/backup.html
>
>
> take also a look at PITR available in the future ( near I hope :-) )
> 8.0:
>
> http://developer.postgresql.org/docs/postgres/backup-online.html
>
>
>
> Regards
> Gaetano Mendola
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 G u i d o B a r o s i o wrote: | On this issue, | | | | Is a good idea to ln -s the pg_xlog directory onto another place, while the postmaster is running? I don't think so | I agree with pablo, documentation at this point is not easy to found. I disagree instead: 1) Go on the first doc page: http://www.postgresql.org/docs/7.4/interactive/index.html 2) Search for backups 3) The first 4 entries are what you have to know Please do not take this as RTFM Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBLwQR7UpzwH2SGd4RAltoAKCMvcXuH4zbwleLDJaXXZ+tu6Q+/ACg5jB1 KvTAP2TiX5ZWFXiKvSTRIW0= =GS6b -----END PGP SIGNATURE-----
What I meant(And I guess that Pablo asked for this also), was that is not easy to found information or howto work on thepg_xlog directory. I didn't take it as "read the FINE manual" anyway ;) Regards, Guido > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > G u i d o B a r o s i o wrote: > > | On this issue, > | > | > | > | Is a good idea to ln -s the pg_xlog directory onto another place, while the postmaster is running? > > I don't think so > > > > | I agree with pablo, documentation at this point is not easy to found. > > I disagree instead: > > 1) Go on the first doc page: http://www.postgresql.org/docs/7.4/interactive/index.html > 2) Search for backups > 3) The first 4 entries are what you have to know > > Please do not take this as RTFM > > > > > Regards > Gaetano Mendola > > > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.4 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFBLwQR7UpzwH2SGd4RAltoAKCMvcXuH4zbwleLDJaXXZ+tu6Q+/ACg5jB1 > KvTAP2TiX5ZWFXiKvSTRIW0= > =GS6b > -----END PGP SIGNATURE----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
pablobarbachano@yahoo.es writes:
> Now my question: my employer wants to do backups every 12 hours, and
> save the logs evry 2 hours to... Can I simply save the logs
> (cp pg_xlog/* ...) or do I need something more elaborate?
You can't realistically do this in any pre-8.0 release; it's perhaps
doable in theory but there are too many gotchas to consider it a
trustworthy backup technique.
8.0 will have real support for log-based backup. Feel free to try out a
beta --- we could use more people testing this feature.
regards, tom lane