CODEWITHSUNDEEP
postgresqlgreenplumpg-dump
s666
s666

Reputation: 274

copying postgreSQL database, table with same number of rows, but different table size

I dumped the database with:

pg_dump dsmartgpp -f /data1/master/backup/db.sql

and I copied the database with

nohup psql -h gpnm1 -d dsmartgpp_1 -U gpadmin < /data1/master/backup/db.sql

the log information showed some errors about the function and datatype of postgis, such as

ALTER FUNCTION ERROR: function "gidx_out" already exists with same argument types.

ALTER FUNCTION ERROR: type "gidx" already exists

some tables between the two database have the same number or records, but about 1MB difference in size.

Upvotes: 0

Views: 292

Answers (2)

A. Scherbaum
A. Scherbaum

Reputation: 855

If you want to avoid these errors, create the new database (dsmartgpp_1) with template0 as template (empty database). You seem to have PostGIS installed in template1, which gets copied into every new database. Or you installed PostGIS in your new database, before importing the dump.

Either way, create the empty database (dsmartgpp_1) and let the dump install the PostGIS functions.

Oh, one more thing, you can use "-f /data1/master/backup/db.sql" instead of the "<" redirect.

If you want to be super careful, also add "-v ON_ERROR_STOP=1". This will stop at the first error.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246163

That is as expected. Restoring a pg_dump creates a logical copy of the database. In particular, the copy won't be bloated (don't worry – a bit of bloat is healthy).

Upvotes: 2

Related Questions