User Tools

Site Tools


postgresql2

Postgresql2

Various Activities

  • Log in as merlin

$ psql merlin

  • List databases

merlin=> \l

  • Change database to merlin

merlin=> \c merlin

  • List all relations

merlin=> \d

  • List all tables

merlin=> \dt

  • Show structure of table devscan

merlin=> \d+ devscan

  • Select all rows from table devscan

merlin=> select * from devscan;

  • Select some rows from table ally_arthur_log

merlin=> select page, username, request_time from ally_arthur_log where username='blacce' and page='login';
merlin=> select page, username, request_time from ally_arthur_log where page='login' and request_time > '2016-01-21 19:00:00' and request_time < '2016-01-21 23:30:00' order by request_time;
merlin=> select page, full_request, username, hostname, request_time from ally_arthur_log where ( page='attack' or page='view') and request_time > '2016-06-24 18:00:00' and request_time < '2016-06-24 23:30:00' order by request_time;

  • Delete ALL rows from table devscan

merlin=> truncate devscan;

  • Delete only SOME rows from table scan

merlin=> delete from scan where scantype='D'; 

  • Get out

merlin=> \q

Migrating to New Version (Debian 7 to 8)

  • List clusters. It's a bit unclear why we already have a 9.4 clusters.

$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file
9.1 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.1-main.log
9.4 main    5433 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

  • Stop all clusters

$ pg_ctlcluster 9.1 main stop
$ pg_ctlcluster 9.4 main stop

  • Delete the default new cluster (presumably Debian created it during the upgrade)

$ pg_dropcluster

  • Migrate. Make sure you have enough disk space for a full copy! For example, archived merlin databases can grow quite huge.

pg_upgradecluster 9.1 main

  • Update your apps. Once confident that everything works, remove the old cluster.

$ pg_dropcluster 9.1 main
$ apt-get purge postgresql-9.1 postgresql-9.1-bin

Tuning

postgresql2.txt · Last modified: 2016/06/26 07:34 by Volker