[postgis-users] Errors in loading table with geom which was produced with pg-dump

[Regina Obe]

For pg_dump here is a binary dump - it tends to be the most efficient.  Repeat the -t with each table you want in the backup

#-F c means custom format
pg_dump -t public.table1 -t public.table2 --encoding UTF8 -F c -b -v -f db_tables.backup your_database_name

To restore in your other database do below - note this will not work if PostGIS is not installed in same schema as database you backed up from
pg_restore --dbname=your_other_database --jobs=4 --clean --verbose db_tables.backup


A plain text pg_dump would look like this: - -F p is what defines plain text format
pg_dump -t public.table1 -t public.table2 --encoding UTF8 -F p -b -v -f db_tables.sql your_database_name

and then you can restore that with psql since it's just an sql script.  You can also edit the sql script if you need to change schema or table names.

If this is something you do frequently with same database, best way I've found is to  use a foreign server as follows – this assumes postgis is installed in same schema on both databases if you have a table with postgis.

The benfit with fdw is you don't need to restore into the same table.
Do this on the database you want to copy tables to:


CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SHEMA remote_public;

CREATE SERVER svr_remote
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'server_ip_or_host_goes_here', port '5432', dbname 'db_name_goes_here',  updatable 'false');

-- replace with any user that has access and any account you want to give access to
CREATE USER MAPPING FOR postgres SERVER  svr_remote
OPTIONS (user 'postgres', password 'yourpasswordgoeshere');

-- table1, table2,table3 (replace with tables you want)
-- leave out limit to if you want to link all tables
IMPORT FOREIGN SCHEMA public   --replace with schema tables reside in on remote database
LIMIT TO (table1,table2,table3)
FROM SERVER svr_remote INTO remote_public;

CREATE TABLE my_new_table AS 
SELECT * FROM remote_public.table1;




From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shaozhong SHI
Sent: Saturday, January 25, 2020 1:10 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Errors in loading table with geom which was produced with pg-dump

I do not know why.

What I am looking for is a robust and efficient way to do the following.

Make a copy of a table from a database.

Load it into another database.

It needs to work with tables with from.



On Friday, 24 January 2020, Regina Obe <lr at pcorp.us> wrote:
Shao,
 
You are still not providing enough information.
 
We need the exact command you were using on pg_dump.  For example only if you are doing a plain text dump with pg_dump, can you use psql to load it.
A binary or tar dump needs to be loaded with pg_restore.  This piece of information is missing in your below explanation and could be why you are getting invalid commands.
 
Same goes for psql
 
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shaozhong SHI
Sent: Friday, January 24, 2020 6:44 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Errors in loading table with geom which was produced with pg-dump
 
Hi, Raul Marin Rodriguez,
 
We are using PostgreSQL version 10.10 and postgis version 2.4.8 .
 
The command used for dump the data is pg_dump.exe.
 
The command used for import data is psql.exe.
 
The error message is full of invalid commands.
 
Regards,
 
Shao 
 
On Thu, 23 Jan 2020 at 17:28, <rmrodriguez at carto.com> wrote:
On Thu, Jan 23, 2020 at 6:23 PM Shaozhong SHI <shishaozhong at gmail.com> wrote:
>
> I used pg_dump to dump a table with from.  Then, I used psql to load it to another database, but it showed numerous errors.
>
> Why does that happen?

Hi,

You are not providing any information to debug the issue. The
following information would help:
- Postgresql and Postgis version of both the source and target database.
- The command used to dump the data.
- The command used to import the data.
- The error messages.


-- 
Raúl Marín Rodríguez
carto.com
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users