Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\))
Subject: Re: pg_dump and pg_restore relative to the schema
From: "Armand Pirvu (home)" <armand.pirvu@gmail.com>
In-Reply-To: <98DA8456-9D6D-47B0-BEC7-5D3C426C6F72@gmail.com>
Date: Mon, 7 Mar 2016 11:59:23 -0600
Content-Transfer-Encoding: quoted-printable
Message-Id: <66DA16B4-9B84-4392-990A-CF6F62105BCD@gmail.com>
References: <98DA8456-9D6D-47B0-BEC7-5D3C426C6F72@gmail.com>
To: pgsql-admin@postgresql.org
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Oops

Forgot to include the pg_dump


1 - I took the table ddl
/usr/pgsql-9.5/bin/pg_dump --username pgdba --format plain --file  /var/lib=
/pgsql/armandp/csireg.csilogs --schema-only  -d csireg --table=3Dcsi_logs

2 - Dumped the data

/usr/pgsql-9.5/bin/pg_dump --username pgdba --format custom  --file  /var/l=
ib/pgsql/armandp/csireg.csilogs.data --data-only  -d csireg --table=3Dcsi_l=
ogs

3 - Replaced csilog with csidev in csireg.csilogs

4 - In the new db , which has the schema csidev, I created the table using =
the DDL from (3)

5 - pg_restore fails
-bash-4.2$ /usr/pgsql-9.5/bin/pg_restore --username=3Dpgdba --format=3Dc /v=
ar/lib/pgsql/armandp/csireg.csilogs.data --data-only  --dbname=3Dcsilog --t=
able=3Dcsi_logs
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3553; 0 29955 TABLE DATA c=
si_logs pgdba
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "csi_=
logs" does not exist
   Command was: COPY csi_logs (log_id, log_time, log_server_name, severity_=
level, log_message, json_data, stack_trace) FROM stdin;


On Mar 7, 2016, at 11:55 AM, Armand Pirvu (home) <armand.pirvu@gmail.com> w=
rote:

> Hi
>=20
> I have a table in a db called csi_logs and it resides in it;s own schema =
called csilog
> I need to move it to a totally different database but with a different sc=
hema called csidev
>=20
> 1 - I took the table ddl
> /usr/pgsql-9.5/bin/pg_dump --username pgdba --format plain --file  /var/l=
ib/pgsql/armandp/csireg.csilogs --schema-only  -d csireg --table=3Dcsi_logs
>=20
> 2 - Replaced csilog with csidev in csireg.csilogs
>=20
> 3 - In the new db , which has the schema csidev, I created the table usin=
g the DDL from (2)
>=20
> 4 - pg_restore fails
> -bash-4.2$ /usr/pgsql-9.5/bin/pg_restore --username=3Dpgdba --format=3Dc =
/var/lib/pgsql/armandp/csireg.csilogs.data --data-only  --dbname=3Dcsilog -=
-table=3Dcsi_logs
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3553; 0 29955 TABLE DATA=
 csi_logs pgdba
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "cs=
i_logs" does not exist
>    Command was: COPY csi_logs (log_id, log_time, log_server_name, severit=
y_level, log_message, json_data, stack_trace) FROM stdin;
>=20
> Bu the table is there . So is the schema  also embedded in the data file =
? I am going plain format now to see but if that would be the case, why emb=
ed the schema in a data only file ?=20
> Any other gotchas ?
>=20
> Thank you
> Armand
>=20
>=20


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin