Обсуждение: dump using copy failed

Поиск
Список
Период
Сортировка

dump using copy failed

От
Ankur Kaushik
Дата:

Hi ,

When Using pg_dump

Master Database

pg_dump -U postgres -n public  $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gz



dumping Other server Database

Using Restore Command

gunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1

I am Getting Below Error

======
ERROR:  syntax error at or near "&#"
LINE 1: &#2361;

invalid command \N
=======



When I use --insert with pg_dump this works fine and restore the data Properly but insert is slow also there is no batch insert in postgresql.


What I concern using "copy in pg_dump" the field are separated in TAB delimited  can we explicitily ask pg_dump for fiield-terminated by ','  ?

Re: dump using copy failed

От
naveen kumar
Дата:
/pg_dump -U postgres -h <source host> $srcdb | /usr/local/pgsql/bin/psql -p $port -U postgres -h <destination host > $destinationdb

It will help you, i guess.

thanks,

Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator, 
Mobile Number: +91 7755929449. 

On Wed, Jun 24, 2015 at 1:33 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

Hi ,

When Using pg_dump

Master Database

pg_dump -U postgres -n public  $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gz



dumping Other server Database

Using Restore Command

gunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1

I am Getting Below Error

======
ERROR:  syntax error at or near "&#"
LINE 1: &#2361;

invalid command \N
=======



When I use --insert with pg_dump this works fine and restore the data Properly but insert is slow also there is no batch insert in postgresql.


What I concern using "copy in pg_dump" the field are separated in TAB delimited  can we explicitily ask pg_dump for fiield-terminated by ','  ?

Re: dump using copy failed

От
Ankur Kaushik
Дата:


Still getting error ,

The problem is while taking dump the fields are Tab delimited , Due to this while restoring column is mismatch .

When I use --insert with pg_dump this works fine , But not with "copy" command 

Is there a way in pg_dump so that I can take dump using some other delimited instead of Tab delimited .?

Regards
Ankur


On Thu, Jun 25, 2015 at 8:54 PM, naveen kumar <mnaveendba2@gmail.com> wrote:
/pg_dump -U postgres -h <source host> $srcdb | /usr/local/pgsql/bin/psql -p $port -U postgres -h <destination host > $destinationdb

It will help you, i guess.

thanks,

Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator, 
Mobile Number: +91 7755929449. 

On Wed, Jun 24, 2015 at 1:33 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

Hi ,

When Using pg_dump

Master Database

pg_dump -U postgres -n public  $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gz



dumping Other server Database

Using Restore Command

gunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1

I am Getting Below Error

======
ERROR:  syntax error at or near "&#"
LINE 1: &#2361;

invalid command \N
=======



When I use --insert with pg_dump this works fine and restore the data Properly but insert is slow also there is no batch insert in postgresql.


What I concern using "copy in pg_dump" the field are separated in TAB delimited  can we explicitily ask pg_dump for fiield-terminated by ','  ?


Re: dump using copy failed

От
Raghavendra
Дата:
On Wed, Jun 24, 2015 at 1:33 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

Hi ,

When Using pg_dump

Master Database

pg_dump -U postgres -n public  $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gz



dumping Other server Database

Using Restore Command

gunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1


AFAIK, your command is clean and it should work. So, I gave try at my end it worked well without --insert option in pg_dump..

-bash-4.2$ pg_dump -U postgres -n public postgres -c -t foo | gzip -9 > foo_table.gz
Password:

-bash-4.2$ gunzip < foo_table.gz | PGPASSWORD="edb" psql -U postgres -h 172.24.35.71 postgres
SET
SET
SET
DROP TABLE
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 295
 
I am Getting Below Error

======
ERROR:  syntax error at or near "&#"
LINE 1: &#2361;

invalid command \N
=======


​Can you tell the two machines operating system which you are exchanging the data ?​

 

When I use --insert with pg_dump this works fine and restore the data Properly but insert is slow also there is no batch insert in postgresql.


What I concern using "copy in pg_dump" the field are separated in TAB delimited  can we explicitily ask pg_dump for fiield-terminated by ','  ?

There's no delimiter option in pg_dump utility command. If you are looking for delimited data, then you can directly use COPY command on the table.
---
Regards,
Raghavendra
EnterpriseDB Corporation

Re: dump using copy failed

От
Ankur Kaushik
Дата:

PostgreSQL version 9.4

Both server is centos 6.6

Dump file is attached . which I am facing error to restore

On Fri, Jun 26, 2015 at 2:17 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
On Wed, Jun 24, 2015 at 1:33 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

Hi ,

When Using pg_dump

Master Database

pg_dump -U postgres -n public  $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gz



dumping Other server Database

Using Restore Command

gunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1


AFAIK, your command is clean and it should work. So, I gave try at my end it worked well without --insert option in pg_dump..

-bash-4.2$ pg_dump -U postgres -n public postgres -c -t foo | gzip -9 > foo_table.gz
Password:

-bash-4.2$ gunzip < foo_table.gz | PGPASSWORD="edb" psql -U postgres -h 172.24.35.71 postgres
SET
SET
SET
DROP TABLE
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 295
 
I am Getting Below Error

======
ERROR:  syntax error at or near "&#"
LINE 1: &#2361;

invalid command \N
=======


​Can you tell the two machines operating system which you are exchanging the data ?​

 

When I use --insert with pg_dump this works fine and restore the data Properly but insert is slow also there is no batch insert in postgresql.


What I concern using "copy in pg_dump" the field are separated in TAB delimited  can we explicitily ask pg_dump for fiield-terminated by ','  ?

There's no delimiter option in pg_dump utility command. If you are looking for delimited data, then you can directly use COPY command on the table.
---
Regards,
Raghavendra
EnterpriseDB Corporation

Вложения

Re: dump using copy failed

От
Raghavendra
Дата:
On Fri, Jun 26, 2015 at 3:01 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

PostgreSQL version 9.4

Both server is centos 6.6

​Thank you for sharing the information. Yes, I have test the sample case on same two CentOS machines. 
 
Dump file is attached . which I am facing error to restore

 
​I have tried to restore your Dump file on my machine and couldn't able to reproduce the error you are getting.
 
-bash-4.2$ gunzip < /tmp/bus_stop_42.dump.gz | PGPASSWORD='edb' psql -U postgres -h localhost demo
SET
SET
SET
SET
SET
SET
SET
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  sequence "bus_stop_bus_stop_id_seq" does not exist
ERROR:  table "bus_stop" does not exist
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 531
 setval
--------
    550
(1 row)

ALTER TABLE
ERROR:  relation "point_type" does not exist
ERROR:  relation "org_chart" does not exist
-bash-4.2$
Its a blind guess, Did .gz file copied from other Operating system to source machine ?

--Raghav

Re: dump using copy failed

От
Ankur Kaushik
Дата:

What I noticed , I was inserting the data with difference in Column , I recreated the table it works for me on same database

Thanks for the support

On Fri, Jun 26, 2015 at 5:42 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
On Fri, Jun 26, 2015 at 3:01 PM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:

PostgreSQL version 9.4

Both server is centos 6.6

​Thank you for sharing the information. Yes, I have test the sample case on same two CentOS machines. 
 
Dump file is attached . which I am facing error to restore

 
​I have tried to restore your Dump file on my machine and couldn't able to reproduce the error you are getting.
 
-bash-4.2$ gunzip < /tmp/bus_stop_42.dump.gz | PGPASSWORD='edb' psql -U postgres -h localhost demo
SET
SET
SET
SET
SET
SET
SET
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  relation "public.bus_stop" does not exist
ERROR:  sequence "bus_stop_bus_stop_id_seq" does not exist
ERROR:  table "bus_stop" does not exist
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 531
 setval
--------
    550
(1 row)

ALTER TABLE
ERROR:  relation "point_type" does not exist
ERROR:  relation "org_chart" does not exist
-bash-4.2$
Its a blind guess, Did .gz file copied from other Operating system to source machine ?

--Raghav