PostgreSQL

From Wildsong
Jump to navigationJump to search

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.

Docker Migration

Using Docker is way better than installing software directly on the machine and makes working with advanced versions far simpler.

I am only using PostgreSQL on Bellman to host PostGIS and Geoserver right now, so follow the instructions here in this page: Geoserver to hook up Postgres + PostGIS + Geoserver + Geowebcache + pgadmin4.

I have notes on how to use Docker Compose to set up PostGIS + Geoserver and I need to migrate over to that setup soon and I need to add the pgadmin4 container described below.

I'm working with Postgres 15 mostly.

First time

I usually have to immediately locate and modify pg_hba.conf and postgresql.conf to tune things. They are in the $PGDATA directory, which is usually /var/lib/postgresql/data/

See below to set a password on the postgres account for remote access.

Command line 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.

Security

There are multiple levels (for me anyway)

  1. Gateway firewall. My EdgeRouter forwards all inbound port 5432 to Bellman.
  2. Bellman firewall. Bellman accepts all outside traffic from selected locations, in this case, my employer's IP address. start_firewall.sh (custom script)
  3. PostgreSQL has to be set to listen on its network connection (not just localhost which is the default) in postgresql.conf
  4. PostgreSQL filters are set in pg_hba.conf - in my case, I allow only specific IP addresses and my local network.

How to do stuff

Q. How do I connect and run commands?

sudo -u postgres psql

Q. How can I debug queries and privileges?? HELP!!

On Debian try 

tail -f /var/log/postgresql/postgresql-*.log

This will help you a LOT.

Q. What version do I have installed?

In psql, 

SELECT version();
PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Q. How do I create a new user?

http://www.postgresql.org/docs/9.1/static/sql-createuser.html

sudo -u postgres psql
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?

sudo -u postgres psql -d template1
ALTER USER postgres WITH PASSWORD 'my_new_password';

Q. What databases are present on this server?

\l or \l+

Adding the' '+' to any command includes extra information, such as the amount of space used.

Q. Create a database and set its owner?

CLI 

createdb -U postgres newdatabase

PSQL 

sudo -u postgres psql
CREATE DATABASE "newdatabase"
ALTER DATABASE "newdatabase" OWNER TO "new_owner"
GRANT ALL ON DATABASE "somedatabase" TO "new_owner";

Q. Enable PostGIS?

sudo -u postgres psql
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
SELECT PostGIS_full_version();
POSTGIS="3.1.2 cbe925d" [EXTENSION] PGSQL="130" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)" TOPOLOGY

Q. Look at current permissions?

In PSQL, use \z -- for example: 

gis_data=# '''\c gis_data'''
gis_data=# '''grant usage on schema clatsop to gis_editor;'''
GRANT
gis_data=# '''set search_path to clatsop,public;'''
SET
gis_data=# '''\z'''
                                               Access privileges
 Schema  |            Name             |   Type   |      Access privileges      | Column privileges | Policies
---------+-----------------------------+----------+-----------------------------+-------------------+----------
 clatsop | county_boundary             | table    |                             |                   |
 clatsop | county_boundary_ogc_fid_seq | sequence |                             |                   |
 (etc...)
 clatsop | web_markers                 | table    | gis_owner=arwdDxt/gis_owner+|                   |
         |                             |          | gis_editor=arwd/gis_owner   |                   |
 public  | geography_columns           | view     | postgres=arwdDxt/postgres  +|                   |
         |                             |          | =r/postgres                 |                   |
 public  | geometry_columns            | view     | postgres=arwdDxt/postgres  +|                   |
etc etc

Q. Make a user into a superuser?

sudo -u postgres psql
ALTER USER "sde" WITH SUPERUSER;

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 SCHEMA or_co_marion CASCADE;
CREATE SCHEMA or_co_marion
  AUTHORIZATION gis_owner;

GRANT ALL ON SCHEMA or_co_marion TO gis_owner;
GRANT USAGE ON SCHEMA or_co_marion TO gis_reader;

See also http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql

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();

Q. How do I create an Esri Enterprise Geodatabase?

ESRI docs

In ArcGIS Pro, use the Create Enterprise Geoprocessing tool with these settings. Log entries indicate it's stuck on the idea of using a user called 'sde', probably as the geodatabase admin, so I created that and ran this again. 

Database Platform: PostgreSQL
Instance: YOUR SERVER NAME
Database: YOU PICK A NAME
Database Administrator: postgres (I had to set a password and enable logins in pg_hba.conf)
Database Administrator Password: xxxxxxxxxx
Geodatabase Administrator Password: xxxxxxxxxx
Tablespace Name: I left this blank
Authorization File: it's in C:/Program Files/ESRI/License10.9/sysgen/keycodes -- copy the file to your desktop so you can access it.

Replication and ArcGIS DataStore

I wrote this up in the README.md file in this github project: https://gitrhub.com/Wildsong/docker-postgres-replication

In this project. I set up a pair of Postgres servers and pgadmin in Docker so I could test everything in a sandbox.

I needed to know more about replication to see if my ArcGIS Datastore was working or not.

The reason I never know if DataStore is working is that I see scary log messages indicating the primary had errors and can't failover to the standby. That's two big problems: a data problem and a failover problem.

Upgrades

To move from 9.0.5 to 9.1.2, I did the dump-and-reload thing. I have only a little data that I need to preserve right now so this is easiest.

  1. Get a list of my databases
  2. Dump the ones with value to me with pg_dump
  3. Stop the old server and start the new one
  4. 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 needed 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

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

I have this old note, no idea if it is still relevant:

Navicat - a GUI front end for PostgreSQL. See http://www.navicat.com/ Includes report generation tools

pgadmin4

Used to be a desktop app, now it's a javascript app that can run from desktop but I have it installed as a docker on Bellman. I used it in my tests of Postgres Replication, see the github referenced above.

See the Geoserver page for how to set it up in Docker Compose or run it separately like this: 

sudo mkdir /var/lib/vastra/pgadmin
sudo touch /var/lib/vastra/servers.json

docker pull dpage/pgadmin4
docker volume create pgadmin_files

docker run -d -p 8889:80 --name=pgadmin4 -v pgadmin_files:/var/lib/pgadmin \
  -v "/var/lib/vastra/pgadmin/servers.json:/servers.json" \
  -e "[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=secretpassword" \
  dpage/pgadmin4 

docker update --restart always pgadmin4

Extensions

PostGIS - spatially enables PostgreSQL. (There is some spatial stuff built in.) PostGIS is one of many PostgreSQL Extensions

ArcGIS

Today with the keycodes from my installation of ArcGIS 11.2, I was able to create a geodatabase and able to load and edit spatial data in it using ArcGIS Pro 3.2.2. It's not registered, so there is no UNDO or versioning. I am okay with this for now. But what about ArcGIS at Pro 3.2.2??

These older notes were written for ArcGIS Enterprise 10.6

Esri uses PostgreSQL internally for its "Data Store". Overall interoperability with PostgreSQL as an Enterprise Geodatabase is better but they still throw up barriers to make sure you pay up.Without the interop extension, you can SEE data in PostGIS but cannot edit it. Without an ArcGIS Enterprise license, you cannot install the SDE extensions into a Postgres database, so you cannot access it that way either. If you don't have a license and you just try to connect to a PostGIS database you will get ERROR: relation "sde.sde_layers" does not exist in your PostgreSQL logs and see no tables in the database.

I published a PostGIS database via WFS-T in GeoServer and then found out that ArcMap does not support WFS! You need the "Production Mapping License". ArcGIS Pro 2.3 "supports" WFS but for READ ONLY. So -- useless.

If you really have to have write access, try looking at this: https://www.gisquirrel.com/home It's �150.00 per seat. It runs on the desktop.

Setting up a connection

Make 100% sure you have username/password correct (duh!) and then select a database. If you don't get the creds in there then you won't be able to pick a database.

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

  1. Access users don't expect case-sensitivity.
  2. 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.

Retrieved from "https://wiki.wildsong.biz/index.php?title=PostgreSQL&oldid=14125"