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

Поиск

Список

Период

Сортировка

dump using copy failed

От

Ankur Kaushik

Дата:

24 июня 2015 г., 11:03:36

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

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

Дата:

25 июня 2015 г., 18:24:32

/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: ह

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

Дата:

26 июня 2015 г., 10:44:46

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

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

Дата:

26 июня 2015 г., 11:48:25

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

DROP TABLE

SET

CREATE TABLE

ALTER TABLE

COPY 295

I am Getting Below Error

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

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

Blog: http://raghavt.blogspot.com/

Re: dump using copy failed

От

Ankur Kaushik

Дата:

26 июня 2015 г., 12:31:34

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

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
Blog: http://raghavt.blogspot.com/

Вложения

bus_stop_42.dump.gz

Re: dump using copy failed

От

Raghavendra

Дата:

26 июня 2015 г., 15:12:49

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

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

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

Дата:

29 июня 2015 г., 11:50:08

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

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

dump using copy failed

Re: dump using copy failed

Re: dump using copy failed

Re: dump using copy failed

Re: dump using copy failed

Вложения

Re: dump using copy failed

Re: dump using copy failed