Category: Databases

How to Install the PostgreSQL Client on Ubuntu 22.04 and 24.04

August 14, 2025

Whether you’re managing cloud-hosted databases or working in a containerized environment, the PostgreSQL client is an essential tool for developers, sysadmins, and database engineers alike. This article walks you through installing the PostgreSQL client on Ubuntu 22.04 (Jammy Jellyfish) and Ubuntu 24.04 (Noble Numbat)—two of the most widely used LTS releases. What Is the PostgreSQL Client? The PostgreSQL client includes tools like psql, which allow you to connect to PostgreSQL servers, run SQL queries, perform backups, and administer remote databases. It’s commonly installed on application servers, staging environments, and development machines to interact with databases hosted elsewhere. Which Version Should I Install? Both Ubuntu 22.04 and 24.04 come with a default version of PostgreSQL in their package repositories: Ubuntu Version Default PostgreSQL Client Version 22.04 (Jammy) 14 24.04 (Noble) 16 If you’re fine using the default version, installation is straightforward. If you need a newer or specific version, you can

How to Set Up a Secure SSH Tunnel for MySQL Access with a Restricted User

February 5, 2025

Introduction In many scenarios, database servers are not exposed directly to the internet for security reasons. However, developers and applications still need to connect to them securely. One of the best solutions is to create an SSH tunnel that forwards local connections to the remote MySQL server. In this guide, we will: Create a restricted SSH user who can only tunnel and not log in interactively. Use /sbin/nologin to prevent shell access. Set up port forwarding for secure MySQL access. Test and verify the tunnel. Step 1: Create the SSH User On the SSH gateway server, create a dedicated user:

sudo adduser mysqltunnel

1	sudo adduser mysqltunnel

Set a strong password when prompted. Step 2: Restrict the User to SSH Tunneling Only Set the User’s Shell to /sbin/nologin To prevent interactive logins, change the user’s shell:

sudo usermod --shell /sbin/nologin mysqltunnel

1	sudo usermod --shell /sbin/nologin mysqltunnel

Restrict the User in SSH Configuration Edit the SSH config file:

sudo nano /etc/ssh/sshd_config

1	sudo nano /etc/ssh/sshd_config

Add the following at the bottom:

Match User mysqltunnel
    AllowTcpForwarding yes
    PermitTTY no
    ForceCommand echo 'This account is restricted to tunneling only.'
    X11Forwarding no
    AllowAgentForwarding no

Match User mysqltunnel

AllowTcpForwarding yes

PermitTTY no

ForceCommand echo 'This account is restricted to tunneling only.'

X11Forwarding no

AllowAgentForwarding no

Add Extra MySQL Root User

February 27, 2020

To add an extra “root” capable MySQL user start by logging in to the server on SSH and connect to MySQL CLI using an existing “root” level account. Next add the new user account:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'somepass';

1	CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'somepass';

Next grant permissions:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION;

1	GRANT ALL PRIVILEGES ON . TO 'myuser'@'localhost' WITH GRANT OPTION;

Now flush the privileges cache:

FLUSH PRIVILEGES;

1	FLUSH PRIVILEGES;

You might want to double check the results with:

SHOW GRANTS FOR 'myuser'@'localhost';

1	SHOW GRANTS FOR 'myuser'@'localhost';

MySQL – Run Command On All Tables

January 1, 2020

Here’s a one-liner to run a SQL command on all tables in a database:

mysql -h localhost --password=mypassword -u root -Nse 'show tables' mydb \
   | while read table; do mysql -h localhost -p --password=mypassword -u root \
   -e "show create table $table" mydb; done

mysql -h localhost --password=mypassword -u root -Nse 'show tables' mydb \

| while read table; do mysql -h localhost -p --password=mypassword -u root \

-e "show create table $table" mydb; done

Replace “show create table” with the command that you wish to execute. Replace “mydb” with your database name. Replace “root” and “mypassword” with you username and password. Replace “localhost” with your database server hostname or IP address.

Unknown collation: ‘utf8mb4_unicode_ci’

December 16, 2018

When trying to import MySQL dumps from newer servers to older servers you may get:

Unknown collation: 'utf8mb4_unicode_ci'

1	Unknown collation: 'utf8mb4_unicode_ci'

or:

Unknown collation: 'utf8mb4_unicode_520_ci'

1	Unknown collation: 'utf8mb4_unicode_520_ci'

The quick and dirty solution is to do a search and replace on the database dump changing “utf8mb4_unicode_ci” and “utf8mb4_unicode_520_ci” to “utf8_unicode_ci”. This can be accomplished with sed:


sed -i 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' your_file.sql
sed -i 's/utf8mb4/utf8/g' your_file.sql

sed -i 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' your_file.sql

sed -i 's/utf8mb4/utf8/g' your_file.sql

Reliable Penguin provides managed web hosting, systems administration, website and server migrations, and expert consulting.

Recent Updates

Fixing WP‑CLI “eval()’d code” Parse Errors on PHP 8.4 During “wp db import”

Force a 404 With a CloudFront Function (Always)

HTTP Basic Auth Is Dead

WordPress 6.8 Speculative Loading Can Break HTTP Basic Auth

Zero‑Trust for WordPress Admin with Cloudflare Access (Email One‑Time Codes)