PostgreSQL db

From gnewarchaeology wiki
Revision as of 17:07, 30 January 2016 by Dncgst (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
PostgreSQL > PostgreSQL db



Connect to database

$ psql -h server -U utente -d database

Create a template_gis

PostgreSQL 9.1 + PostGIS 1.5

$ su postgres
$ psql
# CREATE DATABASE template_gis TEMPLATE template0;
# \q
$ psql -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql -d template_gis
$ psql -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql -d template_gis
$ psql -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql -d template_gis
$ psql template_gis
# UPDATE pg_database SET datistemplate = 'TRUE' WHERE datname = 'template_gis';
# \q
$ exit

PostgreSQL 9.4 + PostGIS 2.1

Create a template_gis_it

PostgreSQL 9.1 + PostGIS 1.5

# CREATE DATABASE template_gis_it TEMPLATE template0 LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8';

PostgreSQL 9.4 + PostGIS 2.1

Create a spatial database

PostgreSQL 9.1 + PostGIS 1.5

$ su postgres
$ createdb database --o utente -T template_gis_it
$ psql database
# GRANT ALL ON geometry_columns TO utente;
# GRANT SELECT ON spatial_ref_sys TO utente;
# GRANT SELECT ON geography_columns TO utente;
# \q
$ exit

PostgreSQL 9.4 + PostGIS 2.1

# CREATE DATABASE newdb;
# \c newdb
# CREATE EXTENSION postgis;
# \q

Drop a database

$ dropdb database

Dump and restore a databse

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

The alternative archive file formats must be used with pg_restore to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To dump a single table named mytab:

$ pg_dump -t mytab mydb > db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved.

If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output.

$ pg_dump -Fc mydb > db.dump
$ dropdb mydb
$ pg_restore -C -d postgres db.dump

To restore a database from a sql dump script into a (freshly created) database named newdb:

$ pg_dump mydb > db.sql
$ psql -d newdb -f db.sql

Upgrade a databse

The dump/reload process is assisted by the postgis_restore.pl script which takes care of skipping from the dump all definitions which belong to PostGIS (including old ones), allowing you to restore your schemas and data into a database with PostGIS installed without getting duplicate symbol errors or bringing forward deprecated objects.

$ pg_dump -h localhost -p 5432 -Fc -b -v -f /somepath/olddb.backup olddb
$ createdb -p 5433 -T template_postgis newdb
$ sudo su postgres
# psql -p 5433 newdb
# psql -p 5433 newdb -c "GRANT ALL ON geometry_columns TO dncgst;"
# psql -p 5433 newdb -c "GRANT ALL ON geography_columns TO dncgst;"
# psql -p 5433 newdb -c "GRANT ALL ON spatial_ref_sys TO dncgst;"
# psql -p 5433 -d newdb -f /usr/share/postgresql/9.4/contrib/postgis-2.1/legacy.sql
# perl /usr/share/postgresql/9.4/contrib/postgis-2.1/postgis_restore.pl /somepath/olddb.backup | psql -p 5433 pirronord 2> /somepath/restore_error.txt
# psql -p 5433 -d newdb -f /usr/share/postgresql/9.4/contrib/postgis-2.1/uninstall_legacy.sql