I’m currently running a Tiny Tiny RSS-Server with nginx as a webserver and MySQL as a database-backend. I already wrote about the setup here. My small weekend-project was to change the backend to Postgresql as this is the recommended database by the author for running tt-rss.
Initially I wanted to migrate the database-contents but it turned out to be more work than expected with little to no benefits. The reason for that is that most settings and your feeds are saved either in the configuration-file (config.php) of tt-rss and the feeds are exported and then imported again after the database-switch.
To be clear: This is not a guide on how to migrate your database-contents from MySQL to Postregsql.
As it turned out, if you don’t migrate the database-contents, it’s quite easy to change the backend and doesn’t take a lot of time. Nonetheless I want to write about it and provide a small guide for anyone who has the urge to migrate away from MySQL.
First things first, install Postgres. I’m on a Centos 6.5 box, so I used yum to install it:
yum install postgresql postgresql-server php-pgsql.x86_64
This installs the server- and client-utilities as well as the module that PHP uses to communicate with the Postgres-database.
Next on, I configured Postgres. As it turns out, the default configuration seemed to be pretty good. Mind you, I only worked with MySQL before and never used Postgres. The default configuration-file is:
/var/lib/pgsql/data/postgresql.conf
The parameters in there are very described in great detail. I only changed some parameters related to logging as the default configuration did not meet my style. I wanted to let Postgres log to /var/log/ and to prepend the date to each log message:
2014-02-17 15:19:09 CET-LOG: database system is ready to accept connections
To accomplish this, I changed the following three lines:
log_directory = '/var/log/pg_log'
log_filename = 'postgresql-%F.log'
log_line_prefix = '%t-'
Next thing I had to configure is the authentication-process. Postgres has fairly advanced authentication-options that are described in detail in the Postgres-docs. It is configured in the file pg_hba.conf. I added the following lines to it:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust
host ttrss ttrss 127.0.0.1/32 md5
The first line means that every local connection from every user to every databases is trusted, so you can login without a password.
The second line means, that the user ttrss can only connect to the database ttrss from the host 127.0.0.1/32 (localhost) with a MD5-encrypted password. These rules are applied top to bottom, so the second line is actually never used, because the first line already applies, when tt-rss connects to the database.
Why add the second line then, you may ask? Because I am going to delete the first line after the initial configuration of the database is done. The reason for that is that any potential attacker that gains access to the server can then connect to the database without a password.
After configuring authentication, I needed to add a database user and the database. To do so, login as the postgres system user and use the createuser command.
[root@machine ]# su - postgres # change user to postgres
-bash-4.1$ createuser ttrss # create a user called ttrss
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
To create the database, login as user postgres and enter psql. This opens the Postgres-shell, similar to mysql -uroot. Then, create the database, also similar to MySQL:
[root@machine ]# psql
postgres=# CREATE DATABASE ttrss;
While logged in, change the password for the user ttrss:
postgres=# \password ttrss
Enter new password:
enter it again:
Note the backslash before password. This is required, as Postgres treats commands with a backslash as internal commands that are not parsed by as SQL-statements.
Now the database is ready to be used. Backup and delete the old configuration-file of tt-rss (config.php). Then open up the tt-rss website and walk trough the installation-process. When done, create a new user, import the OPML-file, edit the settings and activate the plugins. That’s all.