Re: pgdump (9.2.4) not dumping all tables
От | Adrian Klaver |
---|---|
Тема | Re: pgdump (9.2.4) not dumping all tables |
Дата | |
Msg-id | 546E2DB2.8000307@aklaver.com обсуждение исходный текст |
Ответ на | pgdump (9.2.4) not dumping all tables (Marcos Cano <mcano@stsa.info>) |
Список | pgsql-general |
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.klaver@aklaver.com
В списке pgsql-general по дате отправления: