Обсуждение: dump using copy failed
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 ',' ?
/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,
It will help you, i guess.
thanks,
Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator,
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_dumpMaster Databasepg_dump -U postgres -n public $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gzdumping Other server DatabaseUsing Restore Commandgunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1I 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 ',' ?
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_dumpMaster Databasepg_dump -U postgres -n public $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gzdumping Other server DatabaseUsing Restore Commandgunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1I 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 ',' ?
Hi ,When Using pg_dumpMaster Databasepg_dump -U postgres -n public $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gzdumping Other server DatabaseUsing Restore Commandgunzip < $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.
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:
Hi ,When Using pg_dumpMaster Databasepg_dump -U postgres -n public $Source_db -c -t $2 | gzip -9 > $db_path/$2_$1.dump.gzdumping Other server DatabaseUsing Restore Commandgunzip < $DB_PATH/$2_$1.dump.gz | PGPASSWORD="postgres" psql -U postgres -h 10.20.41.182 db1AFAIK, 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.gzPassword:-bash-4.2$ gunzip < foo_table.gz | PGPASSWORD="edb" psql -U postgres -h 172.24.35.71 postgresSETSETSETDROP TABLESETSETSETCREATE TABLEALTER TABLECOPY 295I 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.
Вложения
PostgreSQL version 9.4Both 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
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:
PostgreSQL version 9.4Both server is centos 6.6Thank 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 restoreI 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 demoSETSETSETSETSETSETSETERROR: relation "public.bus_stop" does not existERROR: relation "public.bus_stop" does not existERROR: relation "public.bus_stop" does not existERROR: relation "public.bus_stop" does not existERROR: sequence "bus_stop_bus_stop_id_seq" does not existERROR: table "bus_stop" does not existSETSETSETCREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCEALTER TABLECOPY 531setval--------550(1 row)ALTER TABLEERROR: relation "point_type" does not existERROR: 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