Thread: copy database by copying datafiles ?

copy database by copying datafiles ?

From
Alex Vinogradovs
Date:
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

Re: copy database by copying datafiles ?

From
Thomas Hart
Date:
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)


Re: copy database by copying datafiles ?

From
Richard Broersma Jr
Date:
--- On Mon, 12/24/07, Alex Vinogradovs <AVinogradovs@Clearpathnet.com> wrote:

> P.S. datafiles are 85GB in size, I couldn't really dump
> and restore...

Don't for get the steps of compressing and uncompressing between dump and restore.;)  If the file is still too big, you
canalways use tar to spit the file up to make the transition a bit more manageable. 

Regards,
Richard Broersma Jr.

Re: copy database by copying datafiles ?

From
Alex Vinogradovs
Date:
Sorry guys, was my mistake... I found one file missing in global
tablespace. Copying it there fixed the problem.

Thanks everyone!



On Mon, 2007-12-24 at 14:07 -0800, Richard Broersma Jr wrote:
> --- On Mon, 12/24/07, Alex Vinogradovs <AVinogradovs@Clearpathnet.com> wrote:
>
> > P.S. datafiles are 85GB in size, I couldn't really dump
> > and restore...
>
> Don't for get the steps of compressing and uncompressing between dump and restore.;)  If the file is still too big,
youcan always use tar to spit the file up to make the transition a bit more manageable. 
>
> Regards,
> Richard Broersma Jr.