Tech:Postgres

From Cyclopath

Contents 1 Copying a Database 1.1 Preferred Method 1.2 Manual Method 2 Other notes

[edit] Copying a Database

[edit] Preferred Method

This method is useful when you want a live clone of a database (e.g., copying the production database to your development box).

Use the script db_clone.sh.

Notes:

1. If you are initializing a database from scratch, you will need to manually createdb before the script will work.
2. This method does not copy the apache_event table (which is by far the biggest table, several gigs) by default.

[edit] Manual Method

This is useful when the dump will not be immediately loaded into another database.

Database dumps include all all the needed PostGIS information, so you need only do the filesystem part of the PostGIS installation before loading the dump. Install PostGIS into the same location as on the system where the dump was taken.

To dump database cycling:

pg_dump -U cycling -Fc cycling > cycling.dump

To load a dump (this will overwrite your existing database cycling):

dropdb -U postgres cycling
createdb -U postgres cycling
pg_restore -U postgres -d cycling --disable-triggers cycling.dump
psql -U postgres -d cycling -c 'ANALYZE;'

[edit] Other notes

• If you create a view which contains a geometric column, you’ll need to update the geometry_columns table manually.
• The tables annot_bs, aadt, and byway_rating do not use foreign keys to guarantee that a byway_segment row exists with a matching id. Foreign keys require a unique constraint, but the versioning of byway_segments won’t permit a unique constraint on the id column.
• When running update scripts, use the cycling user rather than the postgres user. Using the postgres user to run these scripts may cause database permissions or other problems.