Обсуждение: DBMS upgrade and backups
Hello. Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to 9.1) I'll loose my backups (base backups and wal files will be useless)? So to go to past after DB upgrade I had to install old version(9.0), recover data and then upgrade DBMS software... What is the preferred way to deal with this issue? -- Best regards, Alexander Pyhalov, system administrator of Computer Center of South Federal University
On Mar 14, 2011, at 7:36 PM, Alexander Pyhalov wrote: > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to 9.1) I'll loose my backups (base backups andwal files will be useless)? Yes, those Backups would not be valid. > So to go to past after DB upgrade I had to install old version(9.0), recover data and then upgrade DBMS software... You won't be able to use the Data Directory of New Version for PG 9.0, you have to use your old backup. > What is the preferred way to deal with this issue? I would recommend to take pg_dump backup with Filesystem Backup before any upgrade. Can you explain, why are you upgrading to 9.1? Currently there is no Prduction release of 9.1. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
Hello. On 03/14/2011 17:38, Vibhor Kumar wrote: You won't be able to use the Data Directory of New Version for PG 9.0, you have to use your old backup. > >> What is the preferred way to deal with this issue? > > I would recommend to take pg_dump backup with Filesystem Backup before any upgrade. > > Can you explain, why are you upgrading to 9.1? Currently there is no Prduction release of 9.1. I'm not upgrading now. I'm going to deploy 9.0 server in a week, but and I'm trying to analyze if it will be possible to upgrade to 9.1 (or later version) in future without lost of backup archive. -- Best regards, Alexander Pyhalov, system administrator of Computer Center of South Federal University
On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov <alp@rsu.ru> wrote: > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to > 9.1) I'll loose my backups (base backups and wal files will be useless)? So > to go to past after DB upgrade I had to install old version(9.0), recover > data and then upgrade DBMS software... > What is the preferred way to deal with this issue? Your file-system based backups will be useless (but they probably were useless to start with, unless you shut down postgres while you took them). Your pg_dump generated backups will generally be useful. There have been occasional cases where an old pg_dump won't load into a current modern postgres install, but I think there were several major versions gap in those cases. My bet is that the dumps from 9.0 will load into 9.1 without issue. If you are not backing up using pg_dump or some form of replication, you should reconsider your backup strategy.
On Mon, Mar 14, 2011 at 1:38 PM, Vick Khera <vivek@khera.org> wrote:
I have used the system level backups and WAL files to clone a database on a second server running the same binaries for PostgreSQL numerous times.
As long as you do a ' pg_start_backup ' before starting the system level file copies
and ' pg_stop_backup ' afterwards, you should be fine.
But you are correct that you can't do that to move from one release to another.
I recently tried loading a pg_dump of a large database dump file (around 20 GB) from 8.2 into 9.0 and it failed.
However restoring the 22GB pg_dumpall file worked.
--On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov <alp@rsu.ru> wrote:Your file-system based backups will be useless (but they probably were
> Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to
> 9.1) I'll loose my backups (base backups and wal files will be useless)? So
> to go to past after DB upgrade I had to install old version(9.0), recover
> data and then upgrade DBMS software...
> What is the preferred way to deal with this issue?
useless to start with, unless you shut down postgres while you took
them).
I have used the system level backups and WAL files to clone a database on a second server running the same binaries for PostgreSQL numerous times.
As long as you do a ' pg_start_backup ' before starting the system level file copies
and ' pg_stop_backup ' afterwards, you should be fine.
But you are correct that you can't do that to move from one release to another.
Your pg_dump generated backups will generally be useful. There have
been occasional cases where an old pg_dump won't load into a current
modern postgres install, but I think there were several major versions
gap in those cases.
I recently tried loading a pg_dump of a large database dump file (around 20 GB) from 8.2 into 9.0 and it failed.
However restoring the 22GB pg_dumpall file worked.
Mike Nolan
nolan@tssi.com