Why?
- I want to install postgres using few commands
- I want to be easy to replicate the environment (another servers)
- I like automation
Important
- The default script has the
listen=*which open your postgres to the internet, read about it in the end of this post - All the ansible code can be found at github.com/huogerac/devops-tools
- You should not commit the data (and passwords) inside the group_vars directly to you repository, one alternative is to use ansible-vault
Requirements
- You must have ansible installed locally, usually all you need to do is
pip install ansible - You must have SSH access to the remove server where you want to install the postgres (I recommend you create an ubuntu user with sudo permission rather than use root directly)
- IMPORTANT: You must use ansible 2.8 or 2.9, I believe the postgres module has changed on the latest ansible. I'll make an update for ansible >= 2.10
The ansible folder structure and files
devops/ansible
├── group_vars 👉 Variables / settings
│ ├── all.yml
│ └── development.yml
├── hosts 👉 The target servers
│ └── development
├── playbook.yml 👉 The entrypoint
└── roles
└── postgres 👉 The installation definition
├── bootstrap.sh 👉 The basic setup
├── handlers
│ └── main.yml
├── tasks
│ └── main.yml
└── templates
└── pg_hba.conf.j2
Note: The idea is that you can create multiple environments by creating just two files, in this way, we can replicate the same installation in many servers:
- group_vars/new_environment.yml (could be production.yml) where you can set the database name and password
- hosts/new_environment (could be production) where you can set the IP of other server
1 - Prepare
Let's say we want to Install the Postgres 13 over our development server which has the IP 17.62.218.1 and have the ubuntu user with permission to do root commands
The first thing we need to do, is to update the package manager and install some basic python (for ansible)
cd devops/ansible
note:
You can git clone git@github.com:huogerac/devops-tools.git or create manually the structure inside your project.
Now, create the file bootstrap.sh file
#!/bin/bash
export PATH=$PATH:/usr/bin
sudo apt-get update
sudo apt-get install -y vim htop build-essential \
libssl-dev libffi-dev net-tools \
python3-dev python3-pip python-setuptools
and run the following command:
ssh ubuntu@17.62.218.1 < bootstrap.sh
2 - Change the variables and host files
create or update the hosts/development with your server PUBLIC IP, and the USER
[development]
17.62.218.1 ansible_python_interpreter=/usr/bin/python3 ansible_user=ubuntu
Set the version of postgres to be installed inside the group_vars/all.yml
---
pg_version: 13
Set the information about the new database inside the group_vars/development.yml
---
database_name: tabnews_dev
database_user: tabnews
database_password: xYkR7891-some-great-pass-here
3 - The ansible role to install Postgres
Basically you need to create or copy all files inside the folder roles/postgres to the same structure in your project. No changes are need here.
Which file has a meaning:
└── roles
└── postgres
├── handlers
│ └── main.yml 👉 start/stop/restart postgres during the installation
├── tasks
│ └── main.yml 👉 commands to automate the installation
└── templates
└── pg_hba.conf.j2 👉 the default configuration (you'll need to review soon or later)
Last but not least, we need to create the playbook.yml which set which role can be used for each host
---
- hosts: [development]
user: ubuntu
become: yes
become_user: root
roles:
- { role: postgres, tags: [postgres] }
4 - Installing postgres
Now that we have all ansible files, it's possible to install or reinstall postgres using just one command line, even if you change the server, changing the host ip and running the command, everything will be installed.
ansible-playbook -i hosts/development --tags postgres playbook.yml
5 - Checking the installation
First, access the server
ssh ubuntu@17.62.218.1
Then, check if postgres is running:
ubuntu@myserver:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2021-07-16 01:21:54 UTC; 17s ago
Process: 243489 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 243489 (code=exited, status=0/SUCCESS)
Jul 16 01:21:54 na-inter.net systemd[1]: Starting PostgreSQL RDBMS...
Jul 16 01:21:54 na-inter.net systemd[1]: Finished PostgreSQL RDBMS...
It's possible to check the port available:
ubuntu@myserver:~$ netstat -nlt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:8000 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp6 0 0 :::80 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
tcp6 0 0 :::5432 :::* LISTEN
ubuntu@na-inter.net:~$
5 - Security
By default postgres can be accessed by the localhost only, and with a quick search, we can find that we can change the listen_addresses inside the postgresql.conf to '*'.
However, if possible, you should change the * (which means ALL the internet) to your private network or perhaps the IP or IPs of the clients instead.
Note: The listen_addresses = * is set by this installation because we have this change inside the roles/postgres/tasks/main.yml, it can be changed for each project
Another thing that can be made to secure the server, is to configure a firewall.
sudo ufw enable
In this way, even though the postgres is open, the firewall doesn't allow the 5432 to be accessed, so we need to allow it
sudo ufw allow 5432
Again, you can make the postgres access public, however, with or without firewall, if possible, make it available just for who is trusted.
Finally, having infrastructure as a code is awesome, however, we doesn't want to commit our database password inside our git repository, right?
So, we can use the ansible-vault to encrypt the variable files:
ansible-vault encrypt group_vars/development.yml
You will be requested to set a password and the file will be encrypted.
Note: If you use a CI or want call this installation using another script, the password can be saved at run time to a file and used like bellow:
ansible-vault decrypt --vault-password-file .vault_pass group_vars/development.yml
I hope this post can be helpful, if you need some extra information, something is not clear or wrong, please, send a comment and let me know!
Cheers!
Links: