Charmed PostgreSQL VM

Channel Revision Published Runs on
latest/stable 345 09 Nov 2023
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 239 09 Feb 2022
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 226 01 Apr 2021
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
14/stable 468 11 Sep 2024
Ubuntu 22.04
14/stable 467 11 Sep 2024
Ubuntu 22.04
14/candidate 468 02 Sep 2024
Ubuntu 22.04
14/candidate 467 02 Sep 2024
Ubuntu 22.04
14/beta 502 23 Oct 2024
Ubuntu 22.04
14/beta 503 23 Oct 2024
Ubuntu 22.04
14/edge 516 12 Nov 2024
Ubuntu 22.04
14/edge 515 12 Nov 2024
Ubuntu 22.04
juju deploy postgresql --channel 14/stable
Show information

Platform:

Ubuntu
22.04 20.04 18.04 16.04 14.04

How to deploy on AWS EC2

Amazon Web Services is a popular subsidiary of Amazon that provides on-demand cloud computing platforms on a metered pay-as-you-go basis. Access the AWS web console at console.aws.amazon.com.

Summary


Install AWS and Juju tooling

Install Juju via snap:

sudo snap install juju

Follow the installation guides for:

  • AWs CLI - the Amazon Web Services CLI

To check they are all correctly installed, you can run the commands demonstrated below with sample outputs:

~$ juju version
3.5.4-genericlinux-amd64

~$ aws --version
aws-cli/2.13.25 Python/3.11.5 Linux/6.2.0-33-generic exe/x86_64.ubuntu.23 prompt/off

Authenticate

Create an IAM account (or use legacy access keys) to operate AWS EC2:

mkdir -p ~/.aws && cat <<- EOF >  ~/.aws/credentials.yaml
credentials:
  aws:
    NAME_OF_YOUR_CREDENTIAL:
      auth-type: access-key
      access-key: SECRET_ACCESS_KEY_ID
      secret-key: SECRET_ACCESS_KEY_VALUE
EOF

Bootstrap Juju controller on AWS EC2

Add AWS credentials to Juju:

juju add-credential aws -f ~/.aws/credentials.yaml

Bootstrap Juju controller (check all supported configuration options):

juju bootstrap aws
Output example
> juju bootstrap aws
Creating Juju controller "aws-us-east-1" on aws/us-east-1
Looking for packaged Juju agent version 3.5.4 for amd64
Located Juju agent version 3.5.4-ubuntu-amd64 at https://juju-dist-aws.s3.amazonaws.com/agents/agent/3.5.4/juju-3.5.4-linux-amd64.tgz
Launching controller instance(s) on aws/us-east-1...
 - i-0f4615983d113166d (arch=amd64 mem=8G cores=2)           
Installing Juju agent on bootstrap instance
Waiting for address
Attempting to connect to 54.226.221.6:22
Attempting to connect to 172.31.20.34:22
Connected to 54.226.221.6
Running machine configuration script...
Bootstrap agent now started
Contacting Juju controller at 54.226.221.6 to verify accessibility...

Bootstrap complete, controller "aws-us-east-1" is now available
Controller machines are in the "controller" model

Now you can run
	juju add-model <model-name>
to create a new model to deploy workloads.

You can check the AWS EC2 instance availability (ensure the right AWS region chosen!):

Create a new Juju model:

juju add-model welcome

(Optional) Increase the debug level if you are troubleshooting charms:

juju model-config logging-config='<root>=INFO;unit=DEBUG'

Deploy charms

The following command deploys PostgreSQL and Data-Integrator (the charm to request a test DB):

juju deploy postgresql
juju deploy data-integrator --config database-name=test123
juju relate postgresql data-integrator

Check the status:

> juju status --relations
Model    Controller     Cloud/Region   Version  SLA          Timestamp
welcome  aws-us-east-1  aws/us-east-1  3.5.4    unsupported  13:33:05+02:00

App              Version  Status  Scale  Charm            Channel        Rev  Exposed  Message
data-integrator           active      1  data-integrator  latest/stable   41  no       
postgresql       14.12    active      1  postgresql       14/stable      468  no       

Unit                Workload  Agent  Machine  Public address  Ports     Message
data-integrator/0*  active    idle   1        50.19.16.57               
postgresql/0*       active    idle   0        54.224.41.249   5432/tcp  Primary

Machine  State    Address        Inst id              Base          AZ          Message
0        started  54.224.41.249  i-0f374435695ffc54c  ubuntu@22.04  us-east-1b  running
1        started  50.19.16.57    i-061e0d10d36c8cffe  ubuntu@22.04  us-east-1a  running

Integration provider                   Requirer                               Interface              Type     Message
data-integrator:data-integrator-peers  data-integrator:data-integrator-peers  data-integrator-peers  peer     
postgresql:database                    data-integrator:postgresql             postgresql_client      regular  
postgresql:database-peers              postgresql:database-peers              postgresql_peers       peer     
postgresql:restart                     postgresql:restart                     rolling_op             peer     
postgresql:upgrade                     postgresql:upgrade                     upgrade                peer  

Once deployed, request the credentials for your newly bootstrapped PostgreSQL database.

For Juju 2.9 use:

juju run-action --wait data-integrator/leader get-credentials

and for newer Juju 3+ use:

juju run data-integrator/leader get-credentials

The output example:

postgresql:
  data: '{"database": "test123", "external-node-connectivity": "true", "requested-secrets":
    "[\"username\", \"password\", \"tls\", \"tls-ca\", \"uris\"]"}'
  database: test123
  endpoints: 172.31.40.178:5432
  password: rmnk0AO4jICAixCL
  uris: postgresql://relation-4:rmnk0AO4jICAixCL@172.31.40.178:5432/test123
  username: relation-4
  version: "14.12"

At this point, you can access your DB inside AWS using the internal IP address. All further Juju applications will use the database through the internal network:

> psql postgresql://relation-4:rmnk0AO4jICAixCL@172.31.40.178:5432/test123
psql (15.6 (Ubuntu 15.6-0ubuntu0.23.10.1), server 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

test123=> 

From here you can use/scale/backup/restore/refresh your newly deployed Charmed PostgreSQL.

Expose database (optional)

If necessary to access DB from outside of AWS (warning: opening ports to public is risky) open the AWS firewall using the simple juju expose functionality:

juju expose postgresql

Once exposed, you can connect your database using the same credentials as above (Important: this time use the EC2 Public IP assigned to the PostgreSQL instance):

> juju status postgresql
...
Unit           Workload  Agent  Machine  Public address  Ports     Message
postgresql/0*  active    idle   0        54.224.41.249   5432/tcp  Primary
...

> psql postgresql://relation-4:rmnk0AO4jICAixCL@54.224.41.249:5432/test123
psql (15.6 (Ubuntu 15.6-0ubuntu0.23.10.1), server 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

test123=> 

To close the public access run:

juju unexpose postgresql

Clean up

Always clean AWS resources that are no longer necessary - they could be costly!

To destroy the Juju controller and remove AWS instance (warning: all your data will be permanently removed):

> juju controllers
Controller      Model  User   Access     Cloud/Region   Models  Nodes    HA  Version
aws-us-east-1*  -      admin  superuser  aws/us-east-1       1      1  none  3.5.4  

> juju destroy-controller aws-us-east-1 --destroy-all-models --destroy-storage --force

Next, check and manually delete all unnecessary AWS EC2 instances, to show the list of all your EC2 instances run the following command (make sure the correct region used!):

aws ec2 describe-instances --region us-east-1 --query "Reservations[].Instances[*].{InstanceType: InstanceType, InstanceId: InstanceId, State: State.Name}" --output table
Output example
-------------------------------------------------------
|                  DescribeInstances                  |
+---------------------+----------------+--------------+
|     InstanceId      | InstanceType   |    State     |
+---------------------+----------------+--------------+
|  i-0f374435695ffc54c|  m7i.large     |  terminated  |
|  i-0e1e8279f6b2a08e0|  m7i.large     |  terminated  |
|  i-061e0d10d36c8cffe|  m7i.large     |  terminated  |
|  i-0f4615983d113166d|  m7i.large     |  terminated  |
+---------------------+----------------+--------------+

List your Juju credentials:

> juju credentials
...
Client Credentials:
Cloud        Credentials
aws          NAME_OF_YOUR_CREDENTIAL
...

Remove AWS EC2 CLI credentials from Juju:

> juju remove-credential aws NAME_OF_YOUR_CREDENTIAL

Finally, remove AWS CLI user credentials (to avoid forgetting and leaking):

rm -f ~/.aws/credentials.yaml

If you expect having several concurrent connections frequently, it is highly recommended to deploy PgBouncer alongside PostgreSQL. For more information, read our explanation about Connection pooling.