Moving Postgres Database

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