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]

Nick schrieb:

Otherwise is there an recognised dump file in the same way that Oracle has
and if so how do I create it?

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]

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

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

Nick

[Daniel Schuchardt]

>

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

Nick

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\PSql\binary\ZZ_13 fielalias.sql" %2

BLOB - export

psql -h %1 -U syncro -c "SELECT lo_export(mm_picture,
'../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_import('../mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) ||
'.blob') WHERE mm_picture IS NOT NULL" %2 syncro
Daniel