Get a PgBouncer up and running
This is part of the PgBouncer Tutorial. Please refer to this page for more information and the overview of the content. The following document will deploy “PgBouncer” together with PostgreSQL server (coming from the separate charm “Charmed PostgreSQL”).
Deploy Charmed PostgreSQL + PgBouncer
To deploy Charmed PostgreSQL + PgBouncer, all you need to do is run the following commands:
juju deploy pgbouncer --channel 1/stable
juju deploy postgresql # --config profile=testing
Tip: the option
--config profile=testingwill decrease RAM requirements.
Juju will now fetch charms from Charmhub and begin deploying it to the LXD VMs. This process can take several minutes depending on how provisioned (RAM, CPU, etc) your machine is.
You can track the progress by running:
juju status --watch 1s
We recommend keeping a separate shell open running this command. That way, you will always have an easily accessible live update of the statuses for all applications deployed in the current juju model.
Wait until the application is ready - when it is ready, juju status will show:
Model Controller Cloud/Region Version SLA Timestamp
tutorial lxd localhost/localhost 3.1.6 unsupported 21:23:37+02:00
App Version Status Scale Charm Channel Rev Exposed Message
pgbouncer unknown 0 pgbouncer 1/stable 76 no
postgresql 14.9 active 1 postgresql 14/stable 336 no Primary
Unit Workload Agent Machine Public address Ports Message
postgresql/0* active idle 0 10.3.217.79 5432/tcp Primary
Machine State Address Inst id Base AZ Message
0 started 10.3.217.79 juju-ca0eed-0 ubuntu@22.04 Running
juju status --watch 1s, enterCtrl+c. If you want to further inspect juju logs, can watch for logs withjuju debug-log. More info on logging at juju logs.
At this stage PgBouncer will stay in blocked state due to missing relation/integration with PostgreSQL DB, let’s integrate them:
juju integrate postgresql pgbouncer
It will change nothing, as pgbouncer is a subordinated charm and it waits for a client to consume DB service, let’s deploy data-integrator and request access to database test123:
juju deploy data-integrator --config database-name=test123
juju integrate data-integrator pgbouncer
In couple of seconds, the status will be happy for entire model and pgbouncer will be running inside data-integrator:
Model Controller Cloud/Region Version SLA Timestamp
tutorial lxd localhost/localhost 3.1.6 unsupported 21:28:14+02:00
App Version Status Scale Charm Channel Rev Exposed Message
data-integrator active 1 data-integrator stable 13 no
pgbouncer 1.18.0 active 1 pgbouncer 1/stable 76 no
postgresql 14.9 active 1 postgresql 14/stable 336 no Primary
Unit Workload Agent Machine Public address Ports Message
data-integrator/0* active idle 1 10.3.217.167
pgbouncer/0* active idle 10.3.217.167
postgresql/0* active idle 0 10.3.217.79 5432/tcp Primary
Machine State Address Inst id Base AZ Message
0 started 10.3.217.79 juju-ca0eed-0 ubuntu@22.04 Running
1 started 10.3.217.167 juju-ca0eed-1 ubuntu@22.04 Running
Access database
The first action most users take after installing PostgreSQL is accessing it. The easiest way to do this is via the PostgreSQL Command-Line Client psql. Connecting to the database requires that you know the values for host, username and password. To retrieve the necessary fields please run data-integrator action get-credentials:
juju run data-integrator/leader get-credentials
Running the command should output:
postgresql:
database: test123
endpoints: localhost:6432
password: 3tjXolB7VNKob2VnvMPXa6Y3
username: relation_id_7
version: "14.9"
To access the PostgreSQL database via PgBouncer go inside data-integrator charm and use the port 6432 on localhost:
juju ssh data-integrator/0 bash
charmed-postgresql.psql -h 127.0.0.1 -p 6432 -U relation_id_7 -W -d test123
Password: 3tjXolB7VNKob2VnvMPXa6Y3
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
Type "help" for help.
test123=>
Inside MySQL list DBs available on the host show databases:
Password for user relation_id_7: VYm6tg2KkFOBj8mP3IW9O821
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
Type "help" for help.
test123=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+---------+---------+---------------------------------
...
test123 | relation-5 | UTF8 | C.UTF-8 | C.UTF-8 | "relation-5"=CTc/"relation-5" +
| | | | | relation_id_7=CTc/"relation-5"+
| | | | | admin=CTc/"relation-5"
...
Ctrl+dor typeexit.
You can now interact with PostgreSQL directly using any SQL Queries. For example entering SELECT VERSION(), CURRENT_DATE; should output something like:
test123=> SELECT VERSION(), CURRENT_DATE;
version | current_date
--------------------------------------------------------------------------------------------------------------------------------------+--------------
PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit | 2023-10-24
(1 row)
Feel free to test out any other PostgreSQL queries. When you’re ready to leave the psql shell you can just type exit. Now you will be in your original shell where you first started the tutorial; here you can interact with Juju and LXD.
Remove the user
To remove the user, remove the relation. Removing the relation automatically removes the user that was created when the relation was created. Enter the following to remove the relation:
juju remove-relation pgbouncer data-integrator
Now try again to connect to the same PgBouncer you just used above:
charmed-postgresql.psql -h 127.0.0.1 -p 6432 -U relation_id_7 -W -d test123
This will output an error message:
psql: error: connection to server at "127.0.0.1", port 6432 failed: FATAL: password authentication failed
As this user no longer exists. This is expected as juju remove-relation pgbouncer data-integrator also removes the user.
Note: data stay remain on the server at this stage!
Relate the the two applications again if you wanted to recreate the user:
juju relate data-integrator pgbouncer
Re-relating generates a new user and password:
juju run data-integrator/leader get-credentials
You can connect to the database with this new credentials. From here you will see all of your data is still present in the database.