Re: copy database by copying datafiles ?

I expect that it is not quite as easy as that.

My advice (as a non-expert) would be to install the same version of pg
onto the target machine, and use etl
(http://en.wikipedia.org/wiki/Extract,_transform,_load) to transfer the
data. Basically you just need a small script (I like PHP myself, but
it's definitely not always the best choice) to read the data from the
source db, and insert it into the target db. Of course with 85gb of
data, this could be a challenge, but I suspect your method of copying
the data directory is not sufficient.

Perhaps you could instead write a script that pg_dump's a table at a
time, and loads it into the target db (you could use pg_dumpall with
appropriate flags to export the db/table structure only, and load from
there).

In the end, as I said, I'm definitely not an expert. However, this is
how I would go about this task. Hopefully I was at least able to give
you some ideas.

Alex Vinogradovs wrote:
> Guys,
>
> I've created a copy of my database to run on a different server
> by copying entire data directory while database instance was stopped.
> Database generally works on the target machine, but I'm getting
> following error when trying to create a plpgsql function :
>
> ERROR: could not open relation 1664/0/1214: No such file or directory
> SQL state: 58P01
>
> I've checked, all the datafiles were coppied correctly. I'm not familiar
> with storage internals, maybe there are some ties to inode numbers,
> and thus such procedures aren't allowed at all ?
>
> P.S. datafiles are 85GB in size, I couldn't really dump and restore...
>
> Thank you!
>
>
> Best regards,
> Alex Vinogradovs
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)