Обсуждение: Moving DB data disk to a different machine
I have a database that I use for testing. The contents are not particularly critical; if I were to lose that data, it would be an annoyance but not a disaster. Thus I do not back up the DB.
My server machine (actually my windows workstation) became very ill, so the IT folks gave me a new workstation, but installed the disk drive from the old workstation in the new one as a secondary drive.
After installing PostgreSQL 8.2 (what we had been using) on this new workstation, I tried simply copying the 'Program Files\Postgresql\8.2\data' directory over from the old disk to the new, but the result is that when I try to bring up the PostgreSQL server, it takes a long time, and then goes down.
I can arrange to recreate my DB from scratch but it would be a bit of a pain.
Is there any easy way to make the old DB directory image just work?
Thanks,
Topher Eliot
christopher.eliot@nagrastar.com
[]
On Mon, Sep 15, 2008 at 1:09 PM, Eliot, Christopher <christopher.eliot@nagrastar.com> wrote: > I have a database that I use for testing. The contents are not particularly > critical; if I were to lose that data, it would be an annoyance but not a > disaster. Thus I do not back up the DB. > > My server machine (actually my windows workstation) became very ill, so the > IT folks gave me a new workstation, but installed the disk drive from the > old workstation in the new one as a secondary drive. > > After installing PostgreSQL 8.2 (what we had been using) on this new > workstation, I tried simply copying the 'Program Files\Postgresql\8.2\data' > directory over from the old disk to the new, but the result is that when I > try to bring up the PostgreSQL server, it takes a long time, and then goes > down. You need to empty out the postgresql data directory, then copy everything in from the other disk. What errors are you getting? It's possible you're not using a compatible version. The major version numbers need to match (i.e. 8.2.4 to 8.2.7 is fine, 8.1.13 to 8.2.7 is not) and they need to be compiled the same way. I.e. if you had integer timestamps in one you need it in the other.
On Mon, Sep 15, 2008 at 1:09 PM, Eliot, Christopher <christopher.eliot@nagrastar.com> wrote: > My server machine (actually my windows workstation) became very ill, so the > IT folks gave me a new workstation, but installed the disk drive from the > old workstation in the new one as a secondary drive. P.s. the pg_controldata program is quite useful here, if you have it installed. sudo -u postgres ./pg_controldata /var/lib/postgresql/8.3/main/ shows me this on my machine: pg_control version number: 833 Catalog version number: 200711281 Database system identifier: 5177107589293985791 Database cluster state: in production pg_control last modified: Mon 15 Sep 2008 08:49:24 AM MDT Latest checkpoint location: 0/498490 Prior checkpoint location: 0/498450 Latest checkpoint's REDO location: 0/498490 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/401 Latest checkpoint's NextOID: 24603 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint: Mon 15 Sep 2008 08:47:07 AM MDT Minimum recovery ending location: 0/0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 2000 Date/time type storage: 64-bit integers Maximum length of locale name: 128 LC_COLLATE: en_US LC_CTYPE: en_US Most all that stuff needs to match up between the two directories.
Yes, in fact I installed postgreSQL, renamed the newly-created "data" directory, and then copied over the old data directory. When that didn't work I renamed my new data directory off to another name and restored the data directory that was created as part of the installation, and was able to successfully bring up the server. I'll look in to the pg_controldata thing you suggested. Topher Eliot christopher.eliot@nagrastar.com [] > -----Original Message----- > From: Scott Marlowe [mailto:scott.marlowe@gmail.com] > > You need to empty out the postgresql data directory, then > copy everything in from the other disk. What errors are you > getting? It's possible you're not using a compatible version. > > The major version numbers need to match (i.e. 8.2.4 to 8.2.7 is fine, > 8.1.13 to 8.2.7 is not) and they need to be compiled the same way. > I.e. if you had integer timestamps in one you need it in the other. >