Thursday, May 23, 2013

Installing PostgreSQL on Fedora for Tiny Tiny RSS

I recently decided to switch my Tiny Tiny RSS database from MariaDB/MySQL to PostgreSQL.  Mainly  because of this post:  MySQL issues: Lock wait timeout exceeded

The bottom line is you get much better performance from Postgresql.  Postgresql uses less system resources and it really isn't a big deal to install or support; it is fairly simple.  I really don't get why it isn't more popular.  If it were a massive pain, I could understand it, but it really isn't.  Go figure!

That said, here we go...
  
As root:
yum install postgresql postgresql-server postgresql-contrib php-pgsql

After the install, you must next initialize the postgresql database:
postgresql-setup initdb

Now start and enable the service, so it will automatically start when you boot: 
systemctl start postgresql.service
systemctl enable postgresql.service

Configure the service account for Tiny Tiny RSS: 
su - postgres
createuser -P -S -D -R -l ttrss_user

When prompted to enter a password, create a new password:
enter new password:  your_password

Create the Tiny Tiny RSS Database:
createdb --owner=ttrss_user ttrss_db

Grant permissions to the ttrss_db: 
psql
grant all on database ttrss_db to ttrss_user;

Now you need to modify the postgresql client authentication configuration file to ensure you can login with all the changes you just made: 
cd /var/lib/pgsql/data

Make a backup of the distribution configuration file: 
cp pg_hba.conf pg_hba.conf.dist
vim pg_hba.conf

Now, change the lines ending in 'ident' to end with 'md5'
:wq

Restart PostgreSQL to activate all the changes:
systemctl restart postgresql.service

Load the Tiny Tiny RSS Postgresql schema:
psql -U ttrss_user -d ttrss_db -f /var/www/html/ttrss/schema/ttrss_schema_pgsql.sql