PostgreSQL
Which version?
Sometimes I compile my own installations of PostgreSQL, I have information on how and why to build from scratch in the PostGIS page. Normally I try to use packages.
I'm up to working with 9.4 now on my Debian installations. I use a nonstandard repo and install it from packages.
CentOS
The reason to use CentOS is that it supports ESRI ArcGIS Server. If you need a real data store for ArcGIS Server then PostgreSQL is the free alternative to using MS SQL Server or Oracle.
Installation on Dart (when it was running CentOs 6.2) went like this, following the instructions on the PostgreSQL download page:
rpm -i http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm yum install postgresql92-server postgresql92-contrib postgresql92-plpython postgresql92-libs postgresql92 pgadmin3_92 yum update service postgresql-9.2 initdb # go edit pg_hba.conf and postgresql.conf in /var/lib/pgsql/9.2/data service postgresql-9.2 start
Debian
Install the package: postgresql-9.4
These are recommended: python-sqlalchemy postgresql-server-dev-all
First time
I usually have to immediately locate and modify pg_hba.conf and postgresql.conf to tune things. On Debian they are in /etc/postgresql/9.4/main/
See below to set a password on the Postgres account.
Standard admin tool
I use pgadmin3 version 1.12 because it is available on every platform and works the same everywhere and it is free. The same client works with both 8.4 and 9.x postgresql.
Command lines tools
psql gives you command access to PostgreSQL. Normally I set my system to trust local connections in the pg_hba.conf file then use psql -U postgres.
There are special psql commands starting with '\' so do '\?' to list them. Typically I use psql when I want to answer a few questions quickly. I also use it with pipes to load data. See the section on shp2psql in the PostGIS page.
Q. What versions do I have installed?
In psql,
SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)
SELECT PostGIS_full_version();
Q. How do I create a new user?
http://www.postgresql.org/docs/9.1/static/sql-createuser.html
psql -U postgres CREATE USER bwilson WITH PASSWORD 'linenopise'; # and while we're in here... GRANT ALL ON DATABASE somedatabase TO bwilson;
Q. How do I change postgres password?
psql -d template1 -U postgres ALTER USER postgres WITH PASSWORD 'newpassword';
Q. What databases are present on this server?
\l or \l+
Q. Create a database and set its owner?
createdb -U postgres newdatabase psql -U postgres ALTER DATABASE newdatabase OWNER TO new_owner GRANT ALL ON DATABASE somedatabase TO new_owner;
Adding the' '+' to any command includes extra information, such as the amount of space used.
Q. What tables are present in a database?
\d or \d+
Q. What columns are present in a table?
\dS TABLENAME
Q. How do I remove all the tables from a schema?
Drop the schema and then recreate it. drop schema x; create schema x; Then you also have to fix permissions on the schema.DROP FUNCTION IF EXISTS remove_all();
Put this in a file called clear_schema.sql and run it with psql -U postgres < clear_schema.sql
CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
rec RECORD;
cmd text;
BEGIN
cmd := '';
FOR rec IN SELECT
'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace
WHERE
relkind = 'S' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace WHERE relkind = 'r' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
|| quote_ident(proname) || '(' || oidvectortypes(proargtypes)
|| ');' AS name
FROM
pg_proc
INNER JOIN
pg_namespace ns
ON
(pg_proc.pronamespace = ns.oid)
WHERE
ns.nspname =
'public'
ORDER BY
proname
LOOP
cmd := cmd || rec.name;
END LOOP;
EXECUTE cmd;
RETURN;
END;
$$ LANGUAGE plpgsql;
Q. How do I do a query?
SQL works, so once I know a database and a table I can do regular SQL queries
\c crbc_spatial;
SELECT name FROM rivers WHERE name IS NOT NULL LIMIT 10;
name
--------------------------
Rock Creek
Long Meadow Creek
Weed Lake
Frenchman Creek
St. Martin Creek
South Fork Tenmile Creek
Sunflower Canyon
Chicken Springs Canyon
Cottonwood Canyon
Wood Canyon
(10 rows)
SELECT COUNT(*) FROM rivers WHERE name LIKE '% River' count ------- 312 (1 row)
More handy queries (Courtesy PostgreSQL 9 Admin. Cookbook, Packt Publishing)
SELECT current_database(); SELECT current_user; SELECT version();
Upgrades
To move from 9.0.5 to 9.1.2, I do the dump-and-reload thing.
Since I have only a little data that I need to preserve right now I will
- Get a list of my databases
- Dump the ones with value to me with pg_dump
- Stop the old server and start the new one
- Load the data into the new server with pg_restore
I might choose to use pg_dumpall if I had lots of valuable data.
In my case I have about 20 databases but only need to move over the one for trac. The rest (1 or 2) I can easily reload from the original shapefiles.
pg_dump -U postgres trac > trac.sql
Now shutdown version 9.0 and set up version 9.1 using the instructions on the PostGIS page. Then load the databases back in
createdb -U postgres trac psql -U postgres CREATE USER trac WITH CREATEDB; \password trac \c trac \i trac.sql
Associated tools and extensions
PostGIS - spatially enables PostgreSQL. (There is some spatial stuff built in.) PostGIS is one of many PostgreSQL Extensions
SpatialKit - a connector to use PostGIS from ArcGIS.
Navicat - a GUI front end for PostgreSQL. See http://www.navicat.com/ Includes report generation tools
MS Access
I don't actually use Access and don't currently have a copy installed anywhere so someone should write up and add comments to this section.
article on using MS Access with PostgreSQL
The crucial aspect here is to install the PostgreSQL ODBC driver on Windows.
Things to note and perhaps deal with
- Access users don't expect case-sensitivity.
- There is no support for booleans in Access so you have to hack around this
Openoffice / Libreoffice Base
Use the SDBC driver. On Ubuntu this means selecting the package in Synaptic and installing it. libreoffice-sdbc-postgresql in my case. On Ubunu Base is not installed by default so I also installed libreoffice-base
The Datasource URL requested in Base should look like this:
host=myhostname.wildsong.biz port=5433 dbname=mydatabase
I have to know my username and password to test the connection. I have to tell it the name of a file to save the settings information in on the local computer.
Most critically at this point I have to know how to use both Base and PostgreSQL. :-)
Use case: Loading a spreadsheet into PostgreSQL
I have received a spreadsheet for the Mapping Vietnam project.
It has a GRID COORD column that must be converted to a point geometry, and it has a bunch of attribute columns.
