Project 701 ~ PostgreSQL

PostgreSQL is an open source, no-sql (object-relational) database (ORDBMS) which is the db Odoo is built upon. I only have experience with MySQL which is entirely different since, its MySQL relation database management system (RDBMS). I’ve never actually used or played around with Postgres so I will be utilizing an awesome blog I found (via quick duckduckgo(ddg) search) which is by the Nanyang Technological University. This blog contains 6 sections with great snippets of quick refence information (I purposely left out the section about PHP cause don’t touch that stuff with a 20ft pole). There is also a reference to Python database programming, which (after a quick ddg search) led me to this rich wiki page on UsingDbApiWithPostgres The best way to solidify something for me is to create a hello world like example that I can refer back to if I ever need it. I keep all of my examples in a public github repo called ptp. Before I create a hello world example I should familiarize myself a bit more with PosgreSQL. Before I begin I will go through the installation process to ensure that Postgresql is up and running on my local machine.

I am using manjaro so my installation process is going to be slightly different. After a quick ddg search I found a simple guide. All that I need to do to install is ~ sudo yay -S postgresql (I am using the yay AUR helper instead of the default pacman). Next I need to set the password for the postgres user with sudo passwd postgres Finally to change to the postgres user (sudo su postgres -l) and initialize the cluster (initdb --local $LANG -E UTF8 -D '/var/lib/postgres/data/').

I got the output ~

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_NZ.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgres/data/ -l logfile start

Time to start the postgresql.service with systemctl enable --now postgresql.service. I am not entirely aware of the difference between starting with systemctl and starting with pg_ctl but that is something that I can look into down the road.

Creating a database is simple enough ~ createdb <dbname>

Likewise for deleting a database ~ dropdb <dbname>

To connect to a database we use the psql command with our databases’ name like so ~ psql <dbname>

A lot of Postgreql is behind the scenes with Odoo, even the creation/duplication/deletion is entirely abstracted behind the web UI. It will still be good to have the references for using the command line.

To create a database in Odoo I simply have to go to X.X.X.X:8069/web/database/manager and from here I have the ability to backup/duplicate/delete/create/restore and even set a master password.