PostgreSQL is an object-relational database management system , snappy abbreviation ‘ORDBMS’. It is often known as postgres and is available on many platforms. It has also been an unequaled pain for the last few weeks and I don’t think it need be. Here are my notes on what to do next time. NOTE all of this is related to installing onto Linux Mint.
Firstly to get postgresql is very simple as it is availabe in the main Personal Package Archive (PPA). This means it can be installed using
~ $ sudo apt-get install postgresql
~ $ sudo apt-get install libpq-dev (Needed for pg Gem)
Installing it this way adds several commands from the terminal such as psql for accessing the database. For security reasons (I believe*) this install will create a new user on your machine called postgres and only that user will have a role when starting psql. Without making changes it is not possible to access the database as the current user and the following is shown.
~ $ psql
psql: FATAL: role “peter” does not exist
To correctly access psql you must act as the postgres user by executing the following command.
This option is however not available to applications such as rake that want to access and create/modify the database. I cam across two problems repeatedly and both could be solved by modifying the test and development environment in database.yml in the Rails project
- Error - "Could not connect to server: No such file or directory"
- Solution - Add line “host: localhost”
- Error - “fe_send auth: no password supplies”
- Solution - Add lines “user: postgres” & “password: <PASSWORD>”
NB. It is possible to change the postgres password as a super user. Open psql and use the command
~# ALTER USER postgres with password '123’;
Issues with the above system come when you push your project. Your postgres user password is saved in the git repository and won’t be the same for all members working on the project. You will also need to add the to each project individually. A far more robust solution is to move the information to environmental variables. Setting the following environmental variables allows the all of the extra lines added to be removed.
- PGHOST=localhost
- PGUSER=postgres
- PGPASSWORD=123
NB. set enviromental variables using the command
echo “export PGHOST=localhost” >> ~/.bash_profile
To finish is this brief but useful cheatsheet.
* If you know better let me know and I will make an update