Thread: Re: Exporting/moving Postgress Instance

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

>
>
> 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