Tag Archives: PostgreSQL

Using PostgreSQL row commit IDs and live stats

Leave a reply

Once again, it’s time to write down some notes for personal use. Let me know if what I’m jotting down here is particularly wrong.

PostgreSQL can tell you what are the commit IDs that touched a particular table. You query the system column named xmin.

prosody=> select xmin from prosody limit 5;
  xmin
--------
 212236
 770460
 770460
 967052
   1493
(5 rows)

You can also ask for the timestamp when this commit was created; however, that requires you to start tracking timestamps for each commit ID.

prosody=> select pg_xact_commit_timestamp(xmin), * from prosody;

ERROR:  could not get commit timestamp data
HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.

This seems to be rather useful for built-in tracking of the modification timestamp and for etags. If the database backend is well structured, it may be possible to structure queries in such a way to quickly check when the results were last modified, and help the web frontend avoid serving and requesting the results. I don’t have a clear way to do it yet, but while I don’t think impact on the database will be significant, it may help shave off some serving bytes or requests to other backends.

Commit IDs also roll over after 32bit, so their use on a high traffic site needs to be closely considered. Then again, by the time you have over four billion writes, your caches will probably otherwise expire anyway.

Table pg_stat_activity is interesting and lets you see the transactions and even queries in flight. This’ll be slightly messy, but click ‘view raw code’ to see the original formatting, and scroll around a bit.

$ sudo -u postgres psql
psql (10.1, server 9.6.10)
Type "help" for help.

postgres=# SELECT pid, query FROM pg_stat_activity;
 pid  |                                                                                                                                                                                                              query                                                                                                                                                            
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 8968 | SELECT o0."id", o0."token", o0."refresh_token", o0."valid_until", o0."user_id", o0."app_id", o0."inserted_at", o0."updated_at" FROM "oauth_tokens" AS o0 WHERE (o0."token" = $1)
...
...
 9869 | SELECT pid, query FROM pg_stat_activity;
(12 rows)
postgres=# SELECT * FROM pg_stat_activity;
 datid  |   datname   | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                                                                                                                                                                              query                       
--------+-------------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 146762 | yyyyyyy_dev | 8968 |   146761 | yyyyyyy |                  | 127.0.0.1   |                 |       47545 | 2018-11-08 23:44:52.761448+00 |                               | 2018-11-08 23:50:33.742608+00 | 2018-11-08 23:50:40.75014+00  |                 |            | idle                |             |              | SELECT u0."id", u0."bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."following", u0."ap_id", u0."avatar", u0."local", u0."info", u0."follower_address", u0."last_refreshed_at", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1)
....
  16391 | prosody     | 8833 |    16386 | prosody  |                  | 10.0.AA.AAA |                 |       51490 | 2018-11-08 23:44:30.819644+00 | 2018-11-08 23:50:28.826344+00 | 2018-11-08 23:50:28.826344+00 | 2018-11-08 23:50:28.826362+00 |                 |            | idle in transaction |             |              | BEGIN
  12409 | postgres    | 9869 |       10 | postgres | psql             |             |                 |          -1 | 2018-11-08 23:48:29.262371+00 | 2018-11-08 23:50:41.487958+00 | 2018-11-08 23:50:41.487958+00 | 2018-11-08 23:50:41.487961+00 |                 |            | active              |             |      1606549 | SELECT * FROM pg_stat_activity;
(12 rows)
But my dog does.

Migrating Prosody from text store and sqlite3 to PostgreSQL

Leave a reply

Assumptions

  • You started off from the basic storage config:
    • Regular data is in filesystem.
    • mam (xep0313) message archive is in SQLite3.
  • You want to transition to using just PostgreSQL.
  • PostgreSQL version is 9.4.

Installation

# # largest version of pgsql you have
~# apt install postgresql-9.4-client

# # get lua-dbi module for pgsql
~# apt install lua-dbi-postgresql

Creating PostgreSQL user

# # PostgreSQL trusts users connecting over unix domain socket to be the
# # same as their local account.
# # Therefore, become postgres -- the admin account.
user:~$ sudo su postgres

# # run user creation
postgres:~$ createuser --interactive
# # name: prosody
# # no other administrative options

# # run postgresql client
postgres:~$ psql

-- create database
CREATE DATABASE prosody;

-- give the prosody user all rights on it
GRANT ALL ON DATABASE prosody TO prosody;

-- in case of connecting over network and using md5 trust,
-- set prosody account password:
ALTER ROLE prosody WITH ENCRYPTED PASSWORD 'here_some_password';

Over network?

Assuming you want to connect over the network, edit /etc/postgresql/9.4/main/pg_hba.conf. Append:

# type, database, user, address, auth method
host prosody prosody 172.16.0.0/16 md5
  • Try to minimize your permitted netmask.
  • Can you configure a more secure auth method than md5? Do so.

Migrate data from filesystem

# # as prosody local user

prosody:$ cd prosody-hg/tools/migrator

# # overwrite the config.
prosody:$ cat > migrator.cfg.lua << _EOF

local data_path = "../../data";

input {
        type = "prosody_files";
        path = data_path;
}
output {
        type = "prosody_sql";
        driver = "PostgreSQL";
        database = "prosody";
        username = "prosody";
        password = "here_some_password";
        host = "database.host.here"; -- this assumes network connection; migration with local user credentials was not attempted.
}

_EOF

# # run the migrator in ~/prosody-hg/tools/migrator
prosody:$ lua prosody-migrator.lua input output

Migrate mam archive from SQLite3

# # as prosody user

prosody:~$ cd prosody-hg/data

# # Having first verified there is nothing in Prosody table...
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosody;'
# # ...drop prosody table. All its data (roster etc) was until now stored on the filesystem.
prosody:$ sqlite3 prosody.sqlite 'DROP TABLE prosody;'

# # dump and massage the sqlite3 output, piping it into psql.
# # psql authenticates as the 'prosody' user and does not require the password.
prosody:$ sqlite3 prosody.sqlite .dump | \
    grep -v 'BEGIN TRANSACTION;' | \
    sed 's/PRAGMA foreign_keys=OFF;/BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED;/' | \
    grep -v sqlite_sequence | \
    awk '/CREATE TABLE/{gsub("`","\"");} 1' | \
    awk '/CREATE UNIQUE INDEX/{gsub("`","\"");} 1' | \
    sed 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | \
    psql

# # manual step :(
# # fix the autoincrement.
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosodyarchive;'
# # use this number + 1 in:
prosody:$ echo 'ALTER SEQUENCE prosodyarchive_sort_id_seq RESTART WITH 123456;' | psql

Update Prosody config

In prosody.cfg.lua:

storage = {
        archive2 = "sql";
}
sql = { driver = "PostgreSQL", database = "prosody", username = "prosody", password = "here_some_password", host = "database.host.here" }