backups and WAL

[p***@yahoo.es]

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Gaetano Mendola]

Post by p***@yahoo.es
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

[G u i d o B a r o s i o]

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,

Post by Gaetano Mendola

Post by p***@yahoo.es
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...

http://www.postgresql.org/docs/7.4/interactive/backup.html
take also a look at PITR available in the future ( near I hope :-) )
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
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Gaetano Mendola]

-----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 ***@postgresql.org)

[G u i d o B a r o s i o]

What I meant(And I guess that Pablo asked for this also), was that is not easy to found information or howto work on the pg_xlog directory.

I didn't take it as "read the FINE manual" anyway ;)

Regards,
Guido

Post by Gaetano Mendola
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
| 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.
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Post by p***@yahoo.es
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend