Install PostgreSQL

If you need to install a SQL database server on Haiku, one option is to use PostgreSQL server.

In this article we will review how to install and setup PostgreSQL server, so that the server will be available at the system start.

Installation

Launch Haiku Depot and search for "postgresql" in the All packages tab :

Select "postgresql12_server" and proceed with install.

Setup

We will use the "/boot/home/postgresql" folder as our default directory for the PostgreSQL server.

In a Terminal type :

mkdir /boot/home/postgresql

Then initialize the default PostgreSQL directories and databases :

initdb -D /boot/home/postgresql/ -U postgres -W

You need to enter the password for the superuser of your server :

Below are the folders and the config files created :

Starting the service

Now let's start the postgreSQL server with the below command :

pg_ctl -D /boot/home/postgresql -l /boot/home/postgresql/postgresql.log start

You can check that the server is ready by typing :

pg_isready

As indicated in the beginning of this article, we will specify the service to be started at the start of the system.

For that edit the "UserBootscript" file with Pe editor :

lpe /boot/home/config/settings/boot/UserBootscript &

Then add the below line at the end of the file :

pg_ctl -D /boot/home/postgresql -l /boot/home/postgresql/postgresql.log start

Great !

Now, the next time you restart Haiku, the PostgreSQL server will be started automatically.

Create your first database

Let's create our first database named "haikudb".

Type the below command and enter the password of the superuser :

createdb -h localhost -e haikudb -U postgres -W

Let's connect to the server with psql (enter again the password of the superuser) :

psql -h localhost -U postgres -W

Then list all the available databases :

\l

As you can see, the "haikudb" database is in the list.

The other databases are the default databases used by PostgreSQL server.

Let's connect to "haikudb" :

\c haikudb

Then create a role named "user". This might be needed for tools like "pg_bench" :

CREATE ROLE "user" WITH LOGIN PASSWORD 'mypassword';

Check the roles available : \du

Now type the below command to create the table named "contact" :

CREATE TABLE contact (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255),
creation_date DATE DEFAULT CURRENT_DATE
);

Then insert 3 contacts as per below :

INSERT INTO contact (name, email) VALUES
('Jean Dujardin', 'jean.dujardin@cinema.com'),
('Marie Curie', 'marie.curie@healthcare.org'),
('Albert Einstein', 'albert.einstein@galaxy.com');

Ok nice !

Query your database

In order to retrieve the contacts from your database, the command is quite simple :

select * from contact;

If you see the above results, it means everything is OK :)

Now quit psql :

\q

Stop the service

If you need to stop the service, type :

pg_ctl -D /boot/home/postgresql stop

You should now be able to install and setup any PostgreSQL database under Haiku :)