Monday, November 20, 2023

Install clustercontrol v2 on ubuntu 22.04 using podman

Clustercontrol is a database cluster management system, developed by severalnines.com, that ease up the work of database cluster deployment and management, using a nice web interface. Please follow below steps to install clustercontrol v2 using podman.

First, install podman
$ sudo apt update && sudo apt install podman -y

Configure podman registries. Add below lines into the end of /etc/containers/registries.conf
[registries.search]
registries = ['docker.io']

Pull the latest clustercontrol image
$ podman pull severalnines/clustercontrol

Create necessary directories
$ mkdir -p clustercontrol/{backups,cmon.d,cmonlib,datadir,prom-conf,prom-data,sshkey}

Create ssh key, and save them into clustercontrol/sshkey
$ ssh-keygen -t ed25519
Generating public/private ed25519 key pair.
Enter file in which to save the key (/home/myuser/.ssh/id_ed25519): /home/myuser/clustercontrol/sshkey/id_ed25519    
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
...

Start clustercontrol
$ podman run -d --name clustercontrol \
-h clustercontrol \
-p 5000:80 \
-p 5001:443 \
-p 9443:9443 \
-p 19501:19501 \
-e DOCKER_HOST_ADDRESS=192.168.10.10 \
-v $PWD/clustercontrol/cmon.d:/etc/cmon.d \
-v $PWD/clustercontrol/datadir:/var/lib/mysql \
-v $PWD/clustercontrol/sshkey:/root/.ssh \
-v $PWD/clustercontrol/cmonlib:/var/lib/cmon \
-v $PWD/clustercontrol/backups:/root/backups \
-v $PWD/clustercontrol/prom-data:/var/lib/prometheus \
-v $PWD/clustercontrol/prom-conf:/etc/prometheus \
severalnines/clustercontrol

Create username, and set password for the user
$ podman exec -it clustercontrol bash
# s9s user --create --generate-key --controller="https://localhost:9501" --group=admins myuser
# s9s user --change-password --new-password=anypassword myuser

Open a web browser, and browse to the ip address of the server with port 5001 for https























Login using the user and password created above


Friday, November 10, 2023

Using psql without entering password

Sometimes, we need to run psql command without entering the password, even though the account is protected with password. The usual situations are, when we are running the psql command in a script, or we have to constantly monitor the output of psql commands using watch. Here is the method on how to achieve that.

Create a .pgpass file inside the user's home directory who's going to access psql without password

$ touch ~/.pgpass

Follow below format to add the user's details into pgpass

hostname:port:database:username:password

$ echo "10.10.10.10:5432:mydatabase:myuser:mysuperlongpassword" > ~/.pgpass

We can also use wildcard, such as *. If you password contains ":" or "\", use "\" to escape them

$ echo "*:*:mydatabase:myuser:mysuperlongpassword" > ~/.pgpass

Give a proper permission to the file, nobody accept the owner of the home directory is allowed to use the file
$ chmod 0600 ~/.pgpass
Now we should be able to use psql to mydatabase as myuser, without entering any password
$ psql -h localhost -U myuser mydatabase
postgres=# 

Saturday, November 4, 2023

Postgresql 16 streaming replication on ubuntu 22.04

Streaming Replication is a feature in PostgreSQL that allows continuous shipping and application of WAL XLOG records to standby servers to keep them current.

Below is the steps to configure streaming replication using postgresql 16 on ubuntu 22.04, with one master and one slave.

First, install postgresql in all nodes by referring here.


In master node, do below steps:

- add this configuration (uncomment wherever necessary) in /etc/postgresql/16/main/postgresql.conf. For one slave node, set max_wal_senders to 3, and add 2 for every additional slave nodes, according to percona.
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
 - create a user for replication
$ sudo su - postgres
postgres@master:~$ createuser --replication -P replicauser 
- allow replication from slave's ip address. Add below line in /etc/postgresql/16/main/pg_hba.conf, assuming our slave node's ip address is 172.17.0.4
host    replication     replicauser             172.17.0.4/32                 scram-sha-256
- Restart postgres
$ sudo systemctl restart postgresql


In slave node, do below steps:

- remove postgresql data directory which is /etc/postgresql/16/main (we can also rename it to save as a backup)

$ sudo su - postgres

postgres@slave:~$ rm -rf /etc/postgresql/16/main

- set a proper permission to the data directory

postgres@slave:~$ chmod 700 /etc/postgresql/16/main

- copy data from master (assuming master's ip address is 172.17.0.3)

postgres@slave:~$ pg_basebackup -h 172.17.0.3 -U replicauser -D /var/lib/postgresql/16/main/

- add standby.signal file inside postgresql data directory, to tell postgresql that this is a standby node

postgres@slave:~$ touch /var/lib/postgresql/16/main/standby.signal

- add below configuration (uncomment wherever necessary) /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

hot_standby = on

primary_conninfo = 'host=172.17.0.3 port=5432 user=replicauser password=1' 

- restart postgresql

$ sudo systemctl restart postgresql


Verify the replication is working 

- in master, check pg_stat_replication table

$ sudo su - postgres

postgres=# select client_addr, state from pg_stat_replication where usename like 'replicauser';






- Check if walsender process is running in master

$ ps -ef | grep wal


 




- Check if walreceiver is running in slave

$ ps -ef | grep wal




We can also create a database in master, and verify that the same database appear in slave almost instantly .

Wednesday, November 1, 2023

Postgresql 16 installation on ubuntu 22.04

Postgresql 16 is the latest postgresql version, released on Sept 2023. To install postgresql 16 on ubuntu 22.04, just follow below steps


First, create a postgresql repository file
$ sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Then, import repository signing key
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package list
$ sudo apt update

Install postgresql
$ sudo apt install postgresql-16 -y

Configure postgresql to listen on all interfaces rather than just localhost
- Edit /etc/postgresql/16/main/postgresql.conf
$ sudo vi /etc/postgresql/16/main/postgresql.conf
- Add below configuration
listen_addresses = '*'
- Save and exit

Start postgresql
$ sudo systemctl start postgresql

Enable postgresql on boot
$ sudo systemctl enable postgresql

Allow postgresql port (5432) on ufw
$ sudo ufw allow 5432/tcp

Access to postgresql database and check version
$ sudo su - postgres
postgres@host:~$ psql
postgres=# select version();

The output should look like below








Type \q to exit psql


Friday, September 29, 2023

View information about audio or video files in command line

One of the command to accomplish this, is called mediainfo. It is not installed by default, so we have to install it first.


To install mediainfo in ubuntu, simply run
$ sudo apt update && sudo apt install mediainfo -y

Once installed, we can check the information of any audio / video file using the command
$ mediainfo myfile.mp4

Some of the information that we can get from a video file



















Some of the information that we can get from an audio file