PostgreSQL Zeugz

Erste Schritte in Postgesql

In die PostgreSQL Console einloggen

sudo -u postgres psql

Zu User postgres werden

sudo su postgres
# und wenn ich schon root bin
su - postgres

Ausführungspfad und PGDATA setzen

In die /etc/profile folgendes rein schreiben:

if [ -d "/usr/lib/postgresql/12/bin" ] ; then
    export PATH="/usr/lib/postgresql/12/bin:$PATH"
fi
if [ -d "/var/lib/postgresql/12/main" ] ; then
    export PGDATA="/var/lib/postgresql/12/main"
fi

/var/lib/postgresql/11/main ist laut /etc/postgresql/11/main/postgresql.conf das richtige Verzeichnis.

Passwort für den Datenbankuser postgres ändern

In die PostgreSQL Console einloggen:

sudo -u postgres psql

Kommando um das Passwort zu ändern:

\password

Dann muss man zwei mal ein Passwort eingeben.
psql mit \q verlassen.

PostgreSQL neu starten:

sudo service postgresql restart

Ab Ubuntu 16.04:

sudo systemctl restart postgresql

Backup & Create & Restore

sudo -u postgres pg_dump my_db_name > dbname197001010000.sql # backup db
sudo -u postgres createdb another_db_name # create db
sudo -u postgres psql another_db_name < dbname197001010000.sql # restore db

wahres Zeug

Autoincrement Wert setzen:

ALTER SEQUENCE my_awesome_seq RESTART WITH 42

…der nächste autoincrement Wert ist 42.

add ENUM value (ALTER TYPE):

ALTER TYPE my_enum ADD VALUE 'orange' AFTER 'red';

Und weil ein ALTER TYPE my_enum DROP VALUE 'red'; (ein Enum Wert löschen) nicht supported ist, muss man löschen und neu erstellen:

DROP TYPE my_enum;
CREATE TYPE my_enum AS ENUM ('orange', 'grey');
ALTER TYPE my_enum OWNER TO postgres;

…kakke wenn es Referenzen gibt 🙁

Unixtime to lesbar

SELECT to_timestamp(1195374767);
-- 2007-11-18 08:32:47.000000

SELECT to_timestamp(1195374767)::date;
-- 2007-11-18

SELECT to_char(to_timestamp(1195374767),'yyyy-mm-dd hh:ii');
-- 2007-11-18 08:77

SELECT EXTRACT(ISOYEAR FROM TIMESTAMP '2007-11-18 08:32:47.000000');
-- 2007

postgresql.org/docs/current/functions-datetime

postgresql.org/docs/current/functions-formatting

CLI db admin

-- list all databases
\list
\l
-- switch databases
\connect database_name
\c database_name

Also:

\l
\c database_name;
select * from public.user;
-- ...weitere Abfragen für database_name
-- wieder raus mit der Taste 'Q'

https://www.postgresql.org/docs/current/app-psql.html

Datenbank URL

postgres://{user}:{password}@{hostname}:{port}/{database-name}