Heads Explode Here
PostGreSQL: installing
It's been quite a while since I last installed Postgres DB, way back on Ubuntu 10.04. Now I'm trying out Manjaro (ArchLinux-based) and I've kinda forgotten some things.
Installation was a breeze:
yaourt -S postgresInitializing a database wasn't too hard. First thing I did, as I was foggy on the specifics, was to try to start it, which failed, of course. However, seeking the status of the process helped a lot (via systemd):
# systemctl status -l postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2015-04-27 05:02:12 PDT; 7s ago
Process: 12198 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=1/FAILURE)
Apr 27 05:02:12 w00 postgres[12198]: "/var/lib/postgres/data" is missing or empty. Use a command like
Apr 27 05:02:12 w00 postgres[12198]: su - postgres -c "initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'"
Apr 27 05:02:12 w00 postgres[12198]: with relevant options, to initialize the database cluster.
Apr 27 05:02:12 w00 systemd[1]: postgresql.service: control process exited, code=exited status=1
Apr 27 05:02:12 w00 systemd[1]: Failed to start PostgreSQL database server.
Apr 27 05:02:12 w00 systemd[1]: Unit postgresql.service entered failed state.
Apr 27 05:02:12 w00 systemd[1]: postgresql.service failed.
Notice the emboldened lines: very helpful. We need to initialize a database cluster first, via: su - postgres -c "initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'", so let's run that except for the locale setting; we're in Canada so it's en_CA.UTF-8:
# su - postgres -c "initdb --locale en_CA.UTF-8
-D '/var/lib/postgres/data'"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_CA.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgres/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /var/lib/postgres/data
or
pg_ctl -D /var/lib/postgres/data -l logfile start
So far, so good.
However, I was completely stumped at the next step. Serves me right for trying to figure it out when I should've been sleeping. "The next step" being actually connecting to the database.
$ psql
psql: FATAL: role "ron" does not exist
$ psql -U postgres
psql: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
Sure, I haven't created a role (aka user) "ron" yet, but why can't I connect as "postgres"?
Let's modify /var/lib/postgres/data/pg_hba.conf so we have PostGres Host-Based Access configured:
nano /var/lib/postgres/data/pg_hba.confThis next line might help (but I thought the defaults should've worked -- I must have been tired):
# TYPE DATABASE USER ADDRESS METHOD
local postgres postgres trust
local postgres postgres trust
Note: I changed the above config setting back to default and it worked; I must have been really tired. But it still doesn't explain the "pg_hba.conf missing entry for user posgres" message above! This is the default:
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
local all all trust
To test it:
sudo -u postgres pg_ctl reload -D /var/lib/postgres/data/
$ psql -U postgres
psql (9.4.1)
Type "help" for help.
postgres=# create role ron with superuser login;
CREATE ROLE
postgres=#
And I'm in as myself:
$ psql -d postgres -U ron
psql (9.4.1)
Type "help" for help.
postgres=# create database ron;
CREATE DATABASE
postgres=#
So, I'm back in, need to re-familiarize myself with The Postgres Way. It's feels really good to be using it again - feels so much more robust than other SQL servers.
It's one of the finest software engineering projects I've ever encountered.
And the documentation is outstanding too.
Next up: PostGIS installation and learn that.
yaourt -S postgis
...
$ psql
...
# CREATE EXTENSION postgis;
CREATE EXTENSION
# SELECT postgis_full_version();
NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
-[ RECORD 1 ]--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgis_full_version | POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.2" LIBJSON="UNKNOWN" RASTER
...
$ psql
...
# CREATE EXTENSION postgis;
CREATE EXTENSION
# SELECT postgis_full_version();
NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
-[ RECORD 1 ]--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgis_full_version | POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.2" LIBJSON="UNKNOWN" RASTER
(1 row)
Add new comment