Re: pgdump (9.2.4) not dumping all tables

On 11/20/2014 09:47 AM, Marcos Cano wrote:
> hello im trying to dump a complete DB, i've been doing something like this.
> (i'm in the process of upgrading from 9.2.4 to 9.3.5)
>
>
> my current DB looks like this:
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> -----------+----------+-----------+-------------+-------+-----------------------
> DB | postgres | UTF8 | en_US.UTF-8 | C | =Tc/postgres +
> | | | | |
> postgres=CTc/postgres+
> | | | | |
> jp=CTc/postgres
>
> having 171 tables
>
> my dump has been done with this:
>
> 0) su postgres -c "/usr/local/pgsql9.2.4/bin/pg_dump -p 5433 -Fc -v $db >
> $backup_path/$db.bkp"

Best practices is to use the later version of pg_dump(9.3.5) to dump the
older database.

>
> 1) then i create the DB (i postgis enable it)

Are you using the same versions of PostGIS on both servers?

> 2) and then do the restore with a postgis perl script
> su postgres -c "export
> PATH=/usr/local/pgsql9.3.5/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
> && perl /dls/gis/postgis-2.1.3/utils/postgis_restore.pl $baclup_file |
> /usr/local/pgsql9.3.5/bin/psql -h localhost -p 5432 -U postgres $db 2>
> $backup_path/$db_upgrade_errors.txt"
>
>
> everything seems to work fine until i noticed that i'm missing 5 tables,
> doing a diff on two files i find out which 5 tables are missing, there is
> nothing special about this tables except that I noticed some empty fields ,
> like this:
>
> id | x | y | name | placetype |
> point_geom
> ------+---+---+-------------------+-----------+----------------------------------------------------
> 1 | | | Km. 223123 RN-09 | 1 |
> 0101000020E6100000F75BB76C0C1A57DCasdaas2F40
> 2 | | | Km. 223120 RN-09 | 1 |
> 0101000020E6100000ECFCasdasdasd1D3FC122F40

What is the schema definition for al_shared_place?

Or to be more specific what are the data types for the fields?

>
> then i try to do an individual dump of the missing tables (which is not so
> tedious giving the fact that it are only 5 tables)
>
> but when i see the dump file those empty fields are translated to something
> like this :
>
> COPY al_shared_place (id, x, y, name, placetype, point_geom) FROM stdin;
> 1 *\N \N * Km. 223123 RN-09 1
> 0101000020E6100000F75BB76C0C1A57DCasdaas2F40
> 2 *\N \N * Km. 223120 RN-09 1
> 0101000020E6100000ECFCasdasdasd1D3FC122F40
>
>
> so when i do the restore of the table
>
> SET
> SET
> SET
> SET
> SET
> SET
> ERROR: relation "al_shared_place" does not exist
> invalid command \N
> invalid command \N
> invalid command \N
> invalid command \N
> invalid command \N
> invalid command \N
>
>
>
> thanks for your help.
>
> NOTE: is important to mention that my DB is a postgis enable DB, i don't
> think this is the issue that's why im asking here because i think its more
> an encoding missmatch or something like that.
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com