Verify that PostgreSQL is installable on Raspbian

[ellerychan]

Install PostgreSQL on a an RPi. Verify that it works. Record the instructions in the ReadMe.

MS development is proceeding on the assumption that PostgreSQL will be the back-end. It's not a big deal to switch, but it would be good to know ASAP so a lot of backup/restore and other admin instructions don't need to be rewritten.

[jawaad-ahmad]

An installation of Postgres shouldn't be more involved than this. If it works, recommend copying the text here to the ReadMe as @ellerychan requested.

Description	Name	Value
Database Name	DBNAME	hsdc
Database User Name	DBUSER	pguser
Database User Password	DBPASS	pghsdc

Update values above to our liking for development. Production values should not be stored on the wiki.

Install PostgreSQL

1. Install package: sudo apt-get install postgresql
2. Verify processes running: ps -ef | grep postgre

Configure PostgreSQL

Maybe some of this can be simplified. I think we can use the default instance, so there should be no need to create a separate instance under ${HSDC_HOME} unless we truly want it living under /opt for some reason.

$ sudo -i
# DNAME=VALUE_FROM_TABLE
# DBUSER=VALUE_FROM_TABLE
# HSDC_HOME=/opt/todo
# export PGDATA=${HSDC_HOME}/db
# mkdir -p ${PGDATA}
# mkdir -m 1777 -p ${HSDC_HOME}/logs
# chown postgres:postgres ${PGDATA}
# su - postgres
postgres$ DNAME=VALUE_FROM_TABLE
postgres$ DBUSER=VALUE_FROM_TABLE
postgres$ HSDC_HOME=/opt/todo
postgres$ export PGDATA=${HSDC_HOME}/db
postgres$ initdb
postgres$ pg_ctl -D ${PGDATA} -l ${HSDC_HOME}/logs/pg.log start
postgres$ createuser -P ${DBUSER}

Enter the password specified in the table above. Answer No to all three prompts:

• 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

  postgres$ createdb ${DBNAME} postgres$ vi ${PGDATA)/pg_hba.conf

In pg_hba.conf, change the METHOD from trust to password for the following connections:

• local
• IPv4

Change the METHOD to ident for the following connection:

• IPv6

Restart PostgreSQL, and then verify the PostgreSQL user can connect to the database:

postgres$ pg_ctl -D ${PGDATA} -l ${HSDC_HOME}/logs/pg.log restart
postgres$ exit
# psql -U ${DBUSER} ${DBNAME}

Verify no error occurred, then press Ctrl+D or type \q to exit psql.

That's it.

If there are problems starting up or there are problems viewing the PostgreSQL log, we might need to add the following somewhere to run at startup (maybe /etc/rc.local?):

mkdir -p /var/log/postgresql
chown 88:88 /var/log/postgresql

This is assuming the "88" is the correct UID and GID for the postgres user listed in /etc/passwd.

Also need to reboot the rPi to make sure the database is running on startup.

We might also need to set up user's ~/.pgpass file for access without entering a password:

$ touch ~/.pgpass
$ vi ~/.pgpass

Paste the following, filling in the appropriate values from the table above:

#hostname:port:database:username:password
localhost:*:${DBNAME}:${DBUSER}:${DBPASS}

Save and exit.

$ chmod 0400 ~/.pgpass

Note that permissions on this file cannot be less restrictive than 0600; otherwise, the file will be ignored as described in the PostgreSQL manual.

[ellerychan]

If PostgreSQL is running on the same host as the Apache/Django MS, only the local port needs to be enabled. The others can be disabled for better security.

Also please check that the PostgreSQL configuration plays well with the Django configuration when Django is running in the Apache server.

[jawaad-ahmad]

Anything left to do for this, or can we mark this as resolved?

[ellerychan]

@jaron42 has gone through this process, so he should be the one to say. Since it is running on the Pi, I would say he was successful.