Обсуждение: Moving Postgres Database
I want to move a windows server 2003 based postgres database from one server to another server, can I just copy and paste the base folder or do I have to do a backup and restore? If so what options work best for creating a file to move?
Edward Coyle
Intellecare, Inc.
Phone-972-919-9000
Cell-214-507-5740
Edward Coyle wrote: > I want to move a windows server 2003 based postgres database from one > server to another server, can I just copy and paste the base folder or > do I have to do a backup and restore? If so what options work best for > creating a file to move? I think if you shut down postgresql on both systems then you can copy/paste the folder structure. However they must be exactly the same versions - ie you can't do this if one system is 8.0.1 and one is 8.1 or even if one is 8.0.1 and one is 8.0.4. Your other alternative is to use pg_dumpall to create an database dump to import on the new server. -- Postgresql & php tutorials http://www.designmagick.com/
On May 31, 2006, at 9:18 PM, Chris wrote: > Edward Coyle wrote: >> I want to move a windows server 2003 based postgres database from >> one server to another server, can I just copy and paste the base >> folder or do I have to do a backup and restore? If so what options >> work best for creating a file to move? > > I think if you shut down postgresql on both systems then you can > copy/paste the folder structure. > > However they must be exactly the same versions - ie you can't do > this if one system is 8.0.1 and one is 8.1 or even if one is 8.0.1 > and one is 8.0.4. Actually, as long as you're not changing major versions (ie: 8.0.x to 8.1.x) you'll be fine. What you can't do is change things like CPU architecture (well, sometimes you can, sometimes you can't). Changing OSes (ie: windows to unix) would also be questionable. PostgreSQL does have some safeguards to make sure you don't end up trashing your data should the binary formats of the database differ, but this isn't something people do very often, so caveat emptor. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, May 31, 2006 at 15:34:46 -0500, Edward Coyle <ecoyle@intellecare.com> wrote: > I want to move a windows server 2003 based postgres database from one server > to another server, can I just copy and paste the base folder or do I have to > do a backup and restore? If so what options work best for creating a file to > move? If the database is shut down when you do this any the systems are running the same versions of postgres on hardware of the same archteciture type this should work.
Jim Nasby wrote: > On May 31, 2006, at 9:18 PM, Chris wrote: >> Edward Coyle wrote: >>> I want to move a windows server 2003 based postgres database from one >>> server to another server, can I just copy and paste the base folder >>> or do I have to do a backup and restore? If so what options work best >>> for creating a file to move? >> >> I think if you shut down postgresql on both systems then you can >> copy/paste the folder structure. >> >> However they must be exactly the same versions - ie you can't do this >> if one system is 8.0.1 and one is 8.1 or even if one is 8.0.1 and one >> is 8.0.4. > > Actually, as long as you're not changing major versions (ie: 8.0.x to > 8.1.x) you'll be fine. I wasn't sure if changes like 8.1.x to 8.1.4 would cause a problem here: http://developer.postgresql.org/docs/postgres/release-8-1-4.html A dump/restore is not required for those running 8.1.X. However, if you are upgrading from a version earlier than 8.1.2, see the release notes for 8.1.2. (I thought I saw something about an internal change somewhere along 8.1.x versions, this might be it). -- Postgresql & php tutorials http://www.designmagick.com/
Chris <dmagick@gmail.com> writes: > Jim Nasby wrote: >> Actually, as long as you're not changing major versions (ie: 8.0.x to >> 8.1.x) you'll be fine. > I wasn't sure if changes like 8.1.x to 8.1.4 would cause a problem here: We have a project policy that we *never* force initdb in a minor release. So the on-disk representation is always compatible. If you were trying to update from 8.1.0 to 8.1.4 on a single machine, you might need to take appropriate steps suggested in the release notes. But moving to another machine (of the same type) doesn't make that harder ... or easier. regards, tom lane