[DebianGIS] pgis09 to pgis10
Forwarded from Lia
Lia Venturato <venturato@faunalia.it>
(unsubscribed to debiangis)
============
Hi all.
We completed successfully our transition from pgis09+pg74 to pgis10+pg80,
using a different approach from Floris.
Here our report, in the hope it can be useful to someone.
Lia
============
Caution: this method changes the owners of tables, schemas, etc.
All grants are kept, but ownership goes to the user (not admin) who restores
the db. In addition, user-created functions may not be restored (if they need
admin privileges).
Case 1: without schemas
- dump of: db (pg_dump -h [hostname] -U [username] -W -c -d -R -v [database]
> database.sql) + geometry_columns data only (via phppgadmin)
- create new db in pg/postgis 8.0/1.0.4 (property of normal_user, with
template_gis)
create database new_db owner normal_user template=template_gis;
- restore as normal_user:
psql -h [hostname] -U [normal_user] -W [new_db] -e < database.sql
you get some errors, because dumped functions (old pgis) cannot be restored,
but that's ok: new_db will have new pgis10 functions+ old data.
Geometry_columns and spatyal_ref_sys are property of postgres
(from template_gis), so restore gives an error. Spatial_ref_sys is to be kept
like this, granting select privileges to the user of the db. Geometry_columns
table must be restored from dump of data, so first change its privileges then
restore
it:
grant select on spatial_ref_sys to normal_user;
grant all on geometry_columns to normal_user;
then, as normal_user:
\i /path/dump/data/geometry_columns.sql
Case 2: with schemas of different owners
In the dump you have the instructions to change owner and set search_path
during restore (set
authorization to nomeutente, set search_path to schemaname). Given that we
have to restore as normal_user,
this is not possible.
We solved the problem by:
- dumping one schema at a time
pg_dump -h [hostname] -U [username] -W -c -d -R -v [database] -n [schemaname]>
database.sql
clean up the dump, removing "set authorization to...., set
search_path.... drop ....." (this helps cleaning up the errors, not to miss
the important ones)
- dumping geometry_columns (only data, via phppgadmin).
The rest is as above, with the following cautions:
restore schema public as normal_user, not admin
log into the db as normal_user
create schemas
set search_path to nomeschema
and restore the dumps
\i /path/to/clean/dump/schema.sql
At the end, grant the right privileges to geographic tables (see above).
--
(Emi)Lia Venturato
venturato@faunalia.it venturato@jabber.org www.faunalia.it
Piazza Garibaldi 4 56025 Pontedera (PI) Italy
Tel: (+39) 347-2770007 Fax (+39) 0587-213742
Linux User: 388585 http://counter.li.org
Reply to: