Thread: pgdump (9.2.4) not dumping all tables
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"
1) then i create the DB (i postgis enable it)
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
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.
Marcos Cano <mcano@stsa.info> writes:
> everything seems to work fine until i noticed that i'm missing 5 tables,
Did you look at the error output from the restore to see if there were
any complaints?
regards, tom lane
might be awkard but there is no file, so i assume 2 things: 1) there was no upgrade errors 2) the script + command is not writing to stderr (i think it is doing it) -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827739.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
On 11/20/2014 10:06 AM, Marcos Cano wrote: > might be awkard but there is no file, so i assume 2 things: > > 1) there was no upgrade errors So to be clear the file below does not exist?: 2>$backup_path/$db_upgrade_errors.txt" or it exists but there is nothing in it? > 2) the script + command is not writing to stderr (i think it is doing it) > > > > > > -- > View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827739.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/20/2014 10:06 AM, Marcos Cano wrote: > might be awkard but there is no file, so i assume 2 things: > > 1) there was no upgrade errors > 2) the script + command is not writing to stderr (i think it is doing it) Aah, meant to add: Is there anything in the Postgres log for the time period of the restore that would help? > > > > > > -- > View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827739.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
i did it again... and the file shows exactly the same lots of : invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N invalid command \N -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827746.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
to answer to Adrian
1) i am using the old version to dump (i will try with the latest)
2) no the postgis version is different. pg9.2.4-> postgis-2.0.3 and
pg9.3.5-> postgis-2.1.3
3) the schema is public
\d+ al_shared_place
Table
"public.al_shared_place"
Column | Type | Modifiers
| Storage | Stats target | Description
------------+-----------------------+---------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default
nextval(('al_landmark_seq'::text)::regclass) | plain | |
x | character varying(15) |
| extended | |
y | character varying(15) |
| extended | |
name | character varying(50) |
| extended | |
placetype | integer |
| plain | |
point_geom | geometry |
| main | |
Indexes:
"al_shared_place_pkey" PRIMARY KEY, btree (id)
"al_sharedplace_geom_idx" gist (point_geom)
Check constraints:
"$1" CHECK (srid(point_geom) = 4326)
"$2" CHECK (geometrytype(point_geom) = 'POINT'::text OR point_geom IS
NULL)
Has OIDs: no
--
View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827747.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, 20 Nov 2014 11:25:10 -0700 (MST) Marcos Cano <mcano@stsa.info> wrote: > i did it again... and the file shows exactly the same lots of : > > invalid command \N > invalid command \N > invalid command \N > invalid command \N > invalid command \N > invalid command \N > invalid command \N > invalid command \N Those errors are unrelated. Based on your earlier message, the table it's trying to insert into doesn't exist, which means that each line of the copy statement becomes that error as the script tries to continue executing. Essentially, all of those are just chain-reaction symptoms of the real error, which should be reported at the very beginning. Got back to the top of that list of errors and find the one that preceeds all of them and you'll start getting to the real cause of things. Based on your earlier post, the table failed to be created ... find the reason that fails and you'll be making headway. There is a switch to psql (-v ON_ERROR_STOP=1) that causes psql to stop at the first error it encounters instead of trying to execute what's in the rest of the file. I don't know why that isn't the default anyway, but enabling that will remove a lot of the red herrings from your error output. -- Bill Moran I need your help to succeed: http://gamesbybill.com
i found this in the file... ERROR: could not access file "$libdir/rtpostgis-2.0": No such file or directory which acording to the firs link I found <http://gis.stackexchange.com/questions/75520/missing-libraries-when-upgrading-to-postgis-2-1-and-postgresql-9-3-1-using-homeb> , is a postgis bug.. i don't know if that is the reason though. but i guess so. :/ -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827750.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/20/2014 10:52 AM, Marcos Cano wrote: > i found this in the file... What file? Remember the list need context for your statements. You are at the computer and see all that goes on. We only know what you tell us and statements without supporting data are hard to troubleshoot. > > ERROR: could not access file "$libdir/rtpostgis-2.0": No such file or > directory > Look like something is looking for the old version of PostGIS(postgis-2.0.3) on the new database cluster where you have postgis-2.1.3 installed. > > which acording to the firs link I found > <http://gis.stackexchange.com/questions/75520/missing-libraries-when-upgrading-to-postgis-2-1-and-postgresql-9-3-1-using-homeb> > , is a postgis bug.. > > i don't know if that is the reason though. > > but i guess so. :/ I would say you will probably have better luck pursuing this issue on the PostGIS list: http://lists.osgeo.org/mailman/listinfo/postgis-users. It seems there are things you need to sort about migrating PostGIS versions before you can get to the dump/restore process. > > > > -- > View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827750.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
yes i'd better look at the postgis list.. i thought for one moment that this was encoding related. thanks for your help -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827760.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, 20 Nov 2014 11:52:23 -0700 (MST) Marcos Cano <mcano@stsa.info> wrote: > i found this in the file... > > ERROR: could not access file "$libdir/rtpostgis-2.0": No such file or > directory > > > which acording to the firs link I found > <http://gis.stackexchange.com/questions/75520/missing-libraries-when-upgrading-to-postgis-2-1-and-postgresql-9-3-1-using-homeb> Are you upgrading to a system that also has PostGIS 2.0? Or does it have a different version of PostGIS installed? Opinions may differ, but I wouldn't call this a bug. If your trying to upgrade two different pieces of software at the same time, you're going to have to know the internals well enough to work things out. Have you tried upgrading PostgreSQL in one step and PostGIS in another? Seems like the more practical way to handle things. Probably the best bet is to upgrade PostGIS first, then migrate to the new version of PostgreSQL -- but that depends on your situation. If that's impractical, you can probably do a pg_dump/restore in 2 stages: first do a pg_dump -s to only dump the schema objects. Manually edit the resultant file to adjust any version-specific PostGIS stuff before loading that into the new server. Then do a pg_dump -a to dump all the data and load that. Whether that works depends on how much has changed bewteen PostGIS versions -- I haven't worked with PostGIS in almost a year, so I don't know for sure if it will work or not. -- Bill Moran I need your help to succeed: http://gamesbybill.com
so i fix it and got it working !!! i followed the best practices of doing the dump with the newest pg_dump version. and now is working thanks everyone for your help -- View this message in context: http://postgresql.nabble.com/pgdump-9-2-4-not-dumping-all-tables-tp5827736p5827821.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Marcos Cano wrote: [missing data after dump/restore of DB with PostGIS] > i found this in the file... > > ERROR: could not access file "$libdir/rtpostgis-2.0": No such file or > directory Could it be that PostGIS was not installed as an extension in the old database, so that the dump contains the individual CREATE FUNCTION statements rather than on "CREATE EXTENSION postgis"? Yours, Laurenz Albe