Exporting/moving Postgress Instance

[Nick]

Hi,

I'm relatively new to postgres but I've managed to get an instance up and
running and built a web site based on it. But I want to move the complete
site to another host not in the same network. If I want to set the schema up
on a new machine is it sufficient to simply copy the data folder (and sub
folders) and then start the instance by pointing to that data folder?
Otherwise is there an recognised dump file in the same way that Oracle has
and if so how do I create it?

Thanks

Nick

[Daniel Schuchardt]

Re: Exporting/moving Postgress Instance

Nick schrieb:
[color=blue]
> Otherwise is there an recognised dump file in the same way that Oracle has
> and if so how do I create it?[/color]

Hi Nick,

you should look at

pg_dump or pg_dumpall in the docs.

(or pg_dump --help)

restore the dumped database with psql.

Daniel

[Nick]

Re: Exporting/moving Postgress Instance

[color=blue]
>
> Hi Nick,
>
> you should look at
>
> pg_dump or pg_dumpall in the docs.
>
> (or pg_dump --help)
>
> restore the dumped database with psql.
>
> Daniel[/color]

Thanks, it worked to export the data. I'll have to see how it goes with the
import.

Nick

[Daniel Schuchardt]

Re: Exporting/moving Postgress Instance

>[color=blue]
>
> Thanks, it worked to export the data. I'll have to see how it goes with the
> import.
>
> Nick
>
>[/color]


Above two samples. Notice that I use commandline param1 (%1) to specify
the host and param2 (%2) to specify the database name. I also never
export with blobs because i want to have a dumpfile for each table. I
export as INSERTS WITH COLUMN NAMES because my databases could have
different version with different column orders.

sample for export :

Table - export

pg_dump -i -h %1 -a -D -U syncro -S syncro -t fieldalias -f
"E:\Setup\P32\P Sql\binary\ZZ_1 3 fielalias.sql" %2

BLOB - export

psql -h %1 -U syncro -c "SELECT lo_export(mm_pi cture,
'../mainmenu.mm_id. '|| CAST(mm_id AS VARCHAR) || '.blob') FROM mainmenu
WHERE mm_picture IS NOT NULL" %2


sample for import :

Table - import

psql -h %1 -f "%P32PATH%\PSql \binary\ZZ_12 sondfunc.sql" %2 syncro

BLOB - import

psql -h %1 -c "UPDATE mainmenu SET
mm_picture=lo_i mport('../mainmenu.mm_id. '|| CAST(mm_id AS VARCHAR) ||
'.blob') WHERE mm_picture IS NOT NULL" %2 syncro


Daniel