[Micronet] PostgreSQL backups

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[Micronet] PostgreSQL backups

Paul Mackinney
Since we've had a nice discussion about MySQL, I thought I'd ask for
thoughts about Postgres. I don't have any databases large enough to
warrant replication, but the  dump documentation is fairly dense with
options. My most likely restore scenario is to want to migrate
db+website to a new (XEN) install. TIA, PM

--
Paul Mackinney
Engineering Pathway
2111ABC Etcheverry Hall
--
Only two things are infinite, the universe and human stupidity, and I'm
not sure about the former.
        Albert Einstein


 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] PostgreSQL backups

Jonathan Felder
The issues are pretty much the same.  Dump, replicate, or LVM snapshots.

However, Postgres also supports continuous backups by allowing you to
backup the WAL files.  Postgres has an option, that you enable, that
points to a program (that you write) that is run every time a WAL file
is written.  How simple or complex your WAL file copy solution is, is up
to you.

So the way it works is you do a full dump of your database and then save
all the WAL files as they are written.  To restore you simply restore
the dump and then playback all the WAL files.

http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

On 7/22/2010 11:09 AM, Paul Mackinney wrote:
> Since we've had a nice discussion about MySQL, I thought I'd ask for
> thoughts about Postgres. I don't have any databases large enough to
> warrant replication, but the  dump documentation is fairly dense with
> options. My most likely restore scenario is to want to migrate
> db+website to a new (XEN) install. TIA, PM
>


 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] PostgreSQL backups

paul rivers

A few things to add:

- Postgresql backups don't have the same edge case issues which MySQL
has.  What is in the WAL will always match what should be applied to the
data files on roll forward.

- You can use WAL archiving for PITR recovery.  It works as advertised.
 For normal backups (i.e. not trying to do an upgrade), this is usually
the most flexible option, and probably results in smaller backup file(s)
before compression than using pg_dump.

- If you are migrating between major versions, you MUST use a logical
dump and load.  You cannot use PITR recovery, which is a physical
backup.  This is, in my opinion, one of the biggest flaws in postgresql.
 I believe this will be fixed in 9.0, currently in beta.

- In postgresql parlance, the versions are labeled x.y.z.  A major
version means x.y has not changed.  If you increment z, a physical
backup is OK.

- If you are upgrading, don't forget to check whether any contrib/
modules have been installed, and be sure the corresponding modules are
installed in the new environment.  I find it easier to run the drop
installation script in the old environment, run pg_dump for the backup,
load the backup into the new environment, then run the install module
scripts for any needed contrib/ modules in the new environment.

- Most pg migrations are easy.  However, I think migrating past 8.2 runs
into some issues as typecasting rules were tightened up.  Be sure to
plan on testing queries; they will error out immediately if there are
any issues.  Fixing is easy-- simply specify the type.  Example: rather
than " where foo='1.2.3.4/5' " you make the type explicit:  " where
foo='1.2.3.4/5'::cidr ".

Regards,
Paul


On 07/22/2010 02:04 PM, Jonathan Felder wrote:

> The issues are pretty much the same.  Dump, replicate, or LVM snapshots.
>
> However, Postgres also supports continuous backups by allowing you to
> backup the WAL files.  Postgres has an option, that you enable, that
> points to a program (that you write) that is run every time a WAL file
> is written.  How simple or complex your WAL file copy solution is, is up
> to you.
>
> So the way it works is you do a full dump of your database and then save
> all the WAL files as they are written.  To restore you simply restore
> the dump and then playback all the WAL files.
>
> http://www.postgresql.org/docs/8.4/static/continuous-archiving.html
>
> On 7/22/2010 11:09 AM, Paul Mackinney wrote:
>> Since we've had a nice discussion about MySQL, I thought I'd ask for
>> thoughts about Postgres. I don't have any databases large enough to
>> warrant replication, but the  dump documentation is fairly dense with
>> options. My most likely restore scenario is to want to migrate
>> db+website to a new (XEN) install. TIA, PM
>>
>
>
>  
> -------------------------------------------------------------------------
> The following was automatically added to this message by the list server:
>
> To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:
>
> http://micronet.berkeley.edu
>
> Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.


 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.