VirtualBox is a free, open source virtualization platform that allows you to create virtual machines that run on your Linux system. These virtual machines act just like their own self-contained computers, and can run other Linux distributions, or even other operating systems entirely, such as Windows, or MS-DOS.

Virtual Machines – Hosts vs Guests

In virtualization terminology, the Host is the computer that runs a virtual machine, and a Guest is the virtualized computer running in the virtual machine.

The VirtualBox software is installed on the host, and is used to manage and run guest virtual machines.

VirtualBox Guest Additions

The VirtualBox Guest Additions is a software package that is installed on the Guest – the virtual machine. It provides drivers to make the guest operating system run properly – allowing it to use the virtual network interface and fully utilize the system resources allocated to it, as well as extra software to make working with the virtual machine easier, such as sharing folders between the guest and host, or allowing your mouse-pointer to move seamlessly between the two.

Installing VirtualBox Guest Additions

Here are the steps you need to follow to install the guest additions in Linux – we’ll do it from the command line, so it will work on both desktop and server distributions.

All of the following commands are to be run on the guest!

Installing Requirements – Red Hat Linux

First, you’ll need to run the following command to install the software required to build and install the guest additions package:

sudo dnf install tar bzip2 kernel-devel kernel-headers perl gcc make elfutils-libelf-devel

If you do not do this, you’ll get an error about not being able to build kernel modules, and the guest additions will not be fully installed.

Installing Requirements – Ubuntu/Debian Linux

If you’re on Ubuntu/Debian, run the following to install dependencies:

sudo apt install build-essential dkms linux-headers-generic

Attach the VirtualBox Guest Additions CD Image

VirtualBox Guest Additions are supplied in a CD image with VirtualBox, and are available to every virtual machine that you create. To attach the CD image so that the guest can read it, open your virtual machine and click Devices in the menu bar, then click Insert Guest Additions CD Image….

Mount the CD Image

If the image does not mount automatically (likely the case if you are using a server-focussed Linux distribution), mount it by running the following commands:

mkdir /media/cdrom
sudo mount /dev/cdrom /media/cdrom

Then, navigate to the mounted CD-ROM by running:

cd /media/cdrom

Install VirtualBox Guest Additions for Linux

Finally, run the following command to perform the installation:

sudo ./VBoxLinuxAdditions.run

You will see something like the following to indicate that the installation has started:

Verifying archive integrity... All good.
Uncompressing VirtualBox 6.1.40 Guest Additions for Linux.
VirtualBox Guest Additions installer

Additional output will be presented, and the process may take several minutes.

When the installation has successfully completed, reboot:

sudo reboot

You can confirm the modules have been installed successfully by running:

lsmod -l | grep -io vboxguest

View the original article from LinuxScrew here: Installing/Upgrading VirtualBox Guest Additions on Linux Desktop/Server

Once you have your PostgreSQL database server up and running, you’ll probably want to tweak the configuration. The most common things you will want to change are the port and host bindings, and memory and file size limits if you want to tweak the performance of your server.

Finding the PostgreSQL Configuration File

The PostgreSQL configuration file is usually named postgresql.conf and by default is located in the data directory of your PostgreSQL installation. The location of the PostgreSQL data directory will vary depending on the Linux distribution you are using, and how you installed PostgreSQL. Generally, however, you should just be able to issue the following command to get the path to the PostgreSQL configuration file for the active PostgreSQL server:

pg_config --sysconfdir

Ubuntu/Debian

The default location for the PostgreSQL configuration file in Ubuntu and Debian Linux is:

/etc/postgresql/<POSTGRESQL_VERSION>/main/postgresql.conf

Note that the path will differ for each version of PostgreSQL installed.

Red Hat/Fedora

The default location for the PostgreSQL configuration file on Red Hat and Fedora based systems is:

/var/lib/pgsql/<POSTGRESQL_VERSION>/data/postgresql.conf.

Note that the path will differ for each version of PostgreSQL installed.

Arch Linux

The default location for the PostgreSQL configuration file in Arch Linux is:

/var/lib/postgres/data/postgresql.conf

Note that as Arch only installs the latest version of PostgreSQL from its package manager, the version number is not included in the path.

Other Linux Distributions

If you cannot find your PostgreSQL configuration file, or you manually installed the server and placed it at a different path, the following command will find all files named postgresql.conf and list their path:

find / -name "postgresql.conf" 2>/dev/null

Editing the PostgreSQL Configuration File

You’ll need to use a text editor to edit the below values in postgresql.conf. nano is the easiest text editor to use for the Linux command line, and can be used to open or create a text file with the following command:

nano /path/to/postgresql.conf

To edit system configuration files, you also need root privileges using the sudo command:

sudo nano /path/to/postgresql.conf

To find a specific configuration line, you can search in nano by pressing CTRL + W

To save and exit in nano, press the CTRL + X keys.

After you have edited PostgreSQl configuration file, you will need to restart the PostgreSQL database service for the changes to take effect.

If the configuration line that you are editing begins with a hash (#), it is commented – and inactive, so you must remove the hash character from the line before changing the value. For example:

# port = 5432

…is commented, whereas

port = 5432

… is not.

Changing the PostgreSQL Database Server Port

There are several reasons why you may want to change the port PostgreSQL is running on – you may want to run multiple PostgreSQL servers from a single server (each requiring their own port), or you may want to use a non-standard port to pass it through your firewall.

To change the PostgreSQL server port, find the following line in postgresql.conf

port = 5432

5432 is the default port for PostgreSQL. Change this to another valid port number and save.

Changing the PostgreSQL Server Host/Address

In short, when a service ‘listens’ on an address, it responds to network requests that match that address only.

By default, PostgreSQL listens on the hostname localhost, allowing only connections from the local machine:

listen_addresses = 'localhost'

To set PostgreSQl to respond on all available network addresses, a wildcard value can be used:

listen_addresses = *

PostgreSQL can also be set to listen only on a specific interface or IP address. You may want to do this if you want to restrict access to the server only to known IP addresses, or if your server is connected to multiple networks (eg public and private) and want to limit access to only a subset of them:

listen_addresses = 192.168.1.10

To function, the listen_addresses value must be for an address associated with the server.

Multiple listen_addresses can be defined, separated by commas, if your server is connected to multiple networks:

listen_addresses = 192.168.1.10,192.168.2.10

Host Names can also be used, and mixed with IP addresses:

listen_addresses = 192.168.1.10,192.168.2.10,localhost,myServerName

Setting the Maximum Number of Server Connections

PostgreSQL can handle a large number of simultaneous database connections, but you may want to limit this if your server regularly becomes overloaded during periods of high demand. Update the maximum number of connections the server will accept using the below configuration line:

max_connections = 100	

Note that once the connection limit is hit, your users will receive errors and not be able to use the database until the number of connections drops, so you may want to implement connection pooling in your code, or reduce the number of queries, to mitigate this.

Setting the PostgreSQL Memory Limit

If you are running your PostgreSQL alongside other services, like a web server, file server, or just running it on your local computer for development and don’t want it using too many resources, you can limit the memory usage using the following configuration line:

shared_buffers = 128MB	

Alternatively, you may want to raise the memory limit if your database gets very busy.

Setting PostgreSQL Temporary File Limits

While performing queries, especially complex ones on large amounts of data, PostgreSQL may need to store temporary files on your servers storage. If your server has a limited amount of space, you can set a limit to the size of these temporary files on the following configuration line:

temp_file_limit = -1	

Note that a value of -1 means that there is no limit!

View the original article from LinuxScrew here: PostgreSQL Configuration: Port/Host, Memory, Temp File Limits

Connect to the PostgreSQL Server as an Administrative User

To change a password for a user in PostgreSQL, you must first connect with the psql client:

psql -h localhost -p 5432 -U postgres

Above, the psql command is used to connect to the PostgreSQL server on localhost, on port 5432, as the default postgres user.

The default postgres user has administrative rights, has permission to alter the passwords of other users. If you are logging in as a different user, they will need these permissions granted to them.

Updating a Users Password in PostgreSQL

Once connected to the PostgreSQL server, you can update a users password using the ALTER USER statement:

ALTER USER user_name WITH PASSWORD 'new_password';

Note that you’ll need to replace user_name and new_password with the username of the user you want to update the password for, and their new password, respectively.

Checking that the Password has Updated Successfully

To check that the password change was successful, you can attempt to log in as the user:

psql -h localhost -p 5432 -U user_namename

After entering this command you will be prompted for a password for the login. If the password change was successful, the login attempt will be successful.

If not, re-check the command you used to change the password, making sure that you have typed the password correctly and that no error occured.

View the original article from LinuxScrew here: How to Change A User Password in PostgreSQL

What are PostgreSQL’s User Roles?

In most database management systems, permission (such as being able to read or write to a database or table within a database) are assigned directly to user accounts in the database system. While this works, it’s not really convenient if you are dealing with a lot of users and a lot of databases.

User roles in PostgreSQL lets you assign permissions to roles rather than directly to users, and then assign those roles to users. This abstraction makes it easier to manage your databases, and improves security as it is less likely that you will accidentally grant the wrong permissions to the wrong user.

The best example of this is being able to update the permission for a role, with those permissions then being applied to all users in that role, rather than having to update (potentially tens or hundreds) of user accounts individually.

Users can have many roles, and the permissions from each will be applicable, as well as any permissions granted to users individually. As PostgreSQL’s permissions are restricted by default, and you are only granting permissions to allow something, there are no conflicts.

How to Create User Roles in PostgreSQL

The CREATE_ROLE statement is used to create new user roles in PostgreSQL:

CREATE ROLE my_role WITH LOGIN;

Note the use of WITH LOGIN – this grants the LOGIN permission to the role from the outset, otherwise, users with that role would not be able to log in unless they had been granted that permission individually.

Granting Additional Permissions to a Role

The GRANT statement is use to grant permissions to a role once it has been created:

GRANT CONNECT, SELECT ON DATABASE my_database TO my_role;

Above, the role my_role is granted permission to CONNECT to the database my_database and SELECT data from it.

A list of available PostgreSQL database permissions can be found here.

Assigning Roles to Users

The GRANT command is also used to assign roles to users:

GRANT my_role TO my_user;

Removing Roles from Users

The REVOKE statement is used to remove a user from a role:

REVOKE my_role FROM my_user;

View the original article from LinuxScrew here: How to Set Up Users and Roles in PostgreSQL

PostgreSQL Log files

PostgreSQL’s log files will contain any error output from the server process, as well as status from the process. These logs will be created in the directory:

/var/logs/

In addition to this, you can log queries themselves for diagnostics and reporting.

Updating the PostgreSQL Configuration File

Modifying PostgreSQL’s logging options is done through its configuration file. You can find the file by running:

sudo -u postgres psql -c 'SHOW config_file'

Note the use of the sudo command to run the command as the posgres system user.

Enabling Logging in PostgreSQL Configuration

Once you have located the configuration file currently in use by PostgreSQL, edit it by running

sudo nano /path/to/postgresql.conf

Then, find the following line:

#log_statement = 'none'

Uncomment it, and change none to all so it looks like the following:

log_statement = 'all'

The other options for this configuration setting are:

• ddl – Logs all definition statements, such as CREATE, ALTER, and DROP.
• mod – Logs all statements from ddl, as well as data-modifying statements like INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their content is appropriate.
• all – Logs all statements.

To save and quit in nano, press CTRL + X.

Query Log Destination

You can also change the destination of the query log files – this directory must be writable by the postgresql user.

Change this by editing the log_destination entry in your PostgreSQL configuration file.

By default, query logs will be stored in the directory pg_log in the PostgreSQL data directory, which can be found by running:

sudo -u postgres psql -c 'SHOW data_directory'

Applying PostgreSQL Configuration Changes

Once you’ve updated your configuration, restart the PostgreSQL service by following our instructions here.

Queries executed will now be logged to the location configured in the previous step. The log files will named postgresql-YYYY-MM-DD_HHMMSS.log and will contain details about each query, including the query string, and execution time.

Query logs can take up a lot of disk space, especially if your database is busy. It’s best to periodically clear these files, or, rotate them out so that old files are regularly cleared out as new ones are created, reducing disk usage. logrotate is included with most Linux distributions and does just this.

To configure logrotate, add a configuration file for PostgreSQL query logging by running:

sudo nano /etc/logrotate/.d/postgresql-query

…and adding the following configuration to the file:

/path/to/postgresql-*.log {
size 50M
rotate 10
compress
delaycompress
missingok
notifempty
copytruncate
}

This configuration will keep only the most recent 10 log files and compress them to save disk space. A limit of 50 megabytes will also be imposed. The logrotate utility will run periodically to rotate the log files according to this configuration.

Once the configuration has been updated, run:

sudo service logrotate restart

…to apply it by restarting the logrotate service.

View the original article from LinuxScrew here: Enabling PostgreSQL Logging and Query Logging

Comments in PostgreSQL Code

When writing the code that forms your PostgreSQL queries, you can leave comments in two ways.

The first is to use two dashes preceding the commented line to leave a single-line comment (—):

-- This is a single line PostgreSQL comment

The second is to use the /* and */ symbols to mark the beginning and end of comments, which can span multiple lines prepended with *:

/* This is a single line comment */

/*
* This is a 
* multiline
* comment
*/

Comments can be placed standalone on their own lines:

/* Get a list of usernames and emails from the table tbl_users  */
SELECT username, email   
FROM tbl_users;

…Or follow at the end of a line of code:

SELECT username, email  /* Get a list of usernames and emails */
FROM tbl_users;         /* from the table tbl_users  */

This method is fine for commenting your inline PostgreSQL queries in your code, or in the scripts that you will run when to populate your database – but be aware – These comments will not carry through to your database after the code is executed – they comment the code only. So, if you comment a file that creates a table, those comments will not appear in the database in any shape or form attached to that table, or otherwise.

The COMMENT Statement for Describing and Annotating Your Databases

If you want to annotate your database, so that the comments are included in the database structure for other users connected to, and managing, the database to see, you need to use the COMMENT statement. This is a great way to keep your database organised, make sure that everyone working on it (or your future self) knows the purpose of each table, column, function, or other part of your database.

Below, a comment is left on the table tbl_users:

COMMENT ON TABLE tbl_users IS 'This is the users table.';

You can comment on pretty much any entity in a PostgreSQL database:

COMMENT ON COLUMN tbl_users.email IS 'User email address';
COMMENT ON INDEX user_id IS 'Enforces uniqueness on users ID';
COMMENT ON PROCEDURE report_user_count () IS 'Runs a report on the current user count';
COMMENT ON FUNCTION my_function (timestamp) IS 'Returns the current timestamp';

Updating Comments

To update a comment, overwrite it:

COMMENT ON TABLE tbl_users IS 'This comment will overwrite any existing comment on the same entity';

Removing Comments

To remove a comment, replace it with a null value:

COMMENT ON TABLE tbl_users IS NULL;

View the original article from LinuxScrew here: How to Comment PostgreSQL Queries/Code, and the PostgreSQL COMMENT Statement

Docker lets you run PostgreSQL without having to install it directly – instead keeping it in an isolated container. This is useful if you want to run different versions of PostgreSQL for different projects, or just don’t want to (or can’t) install it on your system.

The downside of this is that PostgreSQL’s data is stored inside the container – if the container is destroyed or recreated, the data is lost. If data needs to be kept through these processes, the data must be persisted by storing it outside of the Docker container using one of the below two methods.

Method 1: Using Docker Volumes to Persist PostgreSQL Data

Docker volumes allow docker containers to store data on the host file system in a separate file. When the container is deleted, the data stored in the volume of it will remain. As Docker volumes are self-contained themselves (representing a file system in a single file), they are portable, encapsulating all of the data within them as a single file that can be mounted in a running docker container.

Here is an example of creating a Docker volume named postgres-data:

docker volume create postgres-data

Now, when you run a Docker PostgreSQL container, you can attach the volume to it, setting the point in the filesystem in the PostgreSQL container where the volume will be mounted:

docker run --name postgres_server -v postgres-data:/var/lib/postgresql/data -d postgres

This will create a container named postgres_server and mount the postgres-data volume to the /var/lib/postgresql/data directory within the container using the -v option. When PostgreSQL writes to that directory, the data will be written to the volume outside of the container, persisting it.

Docker volumes can be listed by running:

docker volume ls

This is the preferred method for persisting data in Docker if you are looking for the simplest, easiest the manage solution.

Method 2: Using Docker Bind Mounts to Persist PostgreSQL Data

If you want to store your PostgreSQL data outside of Docker completely, and be in full control of it for the purposes of backing it up or moving it about, this alternative method for persisting PostgreSQL data in Docker works well.

Docker bind mounts mount a directory from the host directly to one inside a running Docker container. Changes made in the directory by either the host or the container will be reflected in each – they will be the same.

To create a bind mount, first create the directory on the host that will be mapped to the directory inside the container:

mkdir /path/to/postgres/data

Choose a path that the docker service will be able to write to.

Now, when launching a Docker container, the bind mount can be specified:

docker run --name postgres_server -v /path/to/postgres/data:/var/lib/postgresql/data -d postgres

Like volumes, bind mounts uses the -v option. Above, the path /path/to/postgres/data on the host is mapped to /var/lib/postgresql/data inside the container. When data is written to /var/lib/postgresql/data by PostgreSQL in the container, it is also written to /var/lib/postgresql/data on the host machine, meaning that it will be available if the container is destroyed.

View the original article from LinuxScrew here: How to Persist Data When Using PostgreSQL in Docker Containers

Before you Delete a Database…

There are a few things you should do before you try and delete a PostgreSQL database, especially if you’re working on a production server.

First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later.

Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete databases.

And finally, make sure you’re sure of the name of the database you want to delete, and that there’s nothing you need to keep in it, and that no other databases or systems are relying on it. Deleting cannot be undone (unless you have a backup to restore).

Deleting/Dropping/Destroying a Database in PostgreSQL with the DROP DATABASE Statement

The PostgreSQL DROP DATABASE statement is used to delete a database by name. It’s as easy to use as:

DROP DATABASE database_name;

The database, including all tables, functions, and other data in it will be deleted.

Checking that the Database Exists Before Attempting Deletion

If the database you are trying to delete doesn’t exist, you’ll get an error. This can be avoided by only deleting the database if it exists:

DROP DATABASE IF EXISTS database_name;

Almost too easy!

View the original article from LinuxScrew here: How to Drop/Delete/Destroy a Database in PostgreSQL

Installing Docker

To use Docker, you’ll need to install it on your Linux system, as we detail in our instructions here.

Running a PostgreSQL Database Server in Docker

Once you have installed Docker, you can start pulling images (downloading the image from an image repository). Images are by default pulled from Docker’s public repository on Docker Hub.

Docker has an official PostgreSQL image which can be pulled by running:

docker pull postgres

Pulling a Specific Version of PostgreSQL

Docker images are organized by tags – labels that usually include the version of the primary software in the container, or other notable attributes of it such as additional packages that may be included.

By default, pulling an image will pull the most recent version, tagged latest.

To install a specific version of PostgreSQL, find the image on Docker Hub and click on the ‘View Available Tags‘ button – you’ll get a full list of available images for all versions of PostgreSQL, and the name of the image for that version. For example, to install PostgreSQL 15 specifically, run:

docker pull postgres:15

Running a Docker Image After Pulling It

Once the PostgreSQL image has been pulled (again, Docker lingo for downloaded)m it can be run using the following command:

docker run --name SERVER_NAME -e POSTGRES_PASSWORD=USER_PASSWORD -d postgres

Note that the following values should be replaced:

• SERVER_NAME is a unique name for identifying running the PostgreSQL server container
• USER_PASSWORD should be the password for the postgres user which will be used to connect to the server

More information can be found in the PostgreSQL documentation.

Connecting to the PostgreSQL Server Running in Docker

By default, the container will map it’s internal PostgreSQL port to the default PostgreSQL port on the local system (5432). Connect using the (psql command)[https://www.linuxscrew.com/postgresql-how-to-connect]:

psql -h localhost -p 5432 -U postgres

Stopping and Removing Docker Containers

Once you have finished with your database, the container can be stopped:

docker stop postgres

If you want to free up resources, you can also completely delete/destroy the container. WARNING, THIS WILL REMOVE ANY DATA YOU STORED IN THE DATABASES HOSTED ON THE POSTGRESQL SERVER WITHIN THE CONTAINER:

docker rm postgres

Where is My Data Stored When Working With Docker Containers?

By default, data (including PostgreSQL databases and their contents) are stored inside the container, and will be deleted with it if it is removed or recreated.

If you want your data to persist after removing a docker container, you will need to configure it to store data outside of the container – we cover how to do this in our article here.

View the original article from LinuxScrew here: How to Run PostgreSQL in a Docker Container in Linux

Before you Delete a Table in your Database…

There are a few things you should do before you try and delete a table from your PostgreSQL database, especially if you’re working with data in production.

First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later.

Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete the table in question.

And finally, make sure you’re sure of the name of the column (and the table it’s in) that you want to delete, and that there’s nothing you need to keep in it, and that no other objects in your database are relying on it. Deleting cannot be undone (unless you have a backup to restore).

Deleting a Column from a PostgreSQL Table Using DROP COLUMN

To delete a table column in a PostgreSQL database you will need to use the ALTER TABLE and DROP COLUMN statements:

ALTER TABLE table_name DROP COLUMN column_name;

Multiple columns can be deleted at the same time by listing them, separated by a comma:

ALTER TABLE table_name DROP COLUMN column_1_, column_2;

Deleting a Column Only if it Exists

An error will be returned if you try to delete a column that does not exist. This can be avoided by checking whether the column exists before deleting it:

ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

Deleting a Column and All Associated Objects

If you try to delete a column that is referenced by other objects in the database, an error will be received. To delete a column and all objects that are associated with it, add the CASCADE statement:

ALTER TABLE table_name DROP COLUMN column_name CASCADE;

Be careful when doing this, and make sure you’re aware of exactly what objects rely on the column being deleted, as they will be removed, as will other reliant entities and any data they contain.

Safely Deleting Columns with RESTRICT

To remove a column only if there are no foreign keys or other objects in PostgreSQL that rely on it, you can use the RESTRICT statement:

ALTER TABLE table_name DROP COLUMN column_name RESTRICT;

If the check fails, the column deletion will fail. Note that this is now the default behaviour in PostgreSQL.

Remember, when a table column is removed, so is all of the data in it!

View the original article from LinuxScrew here: How to Drop/Delete/Destroy a Table Column in PostgreSQL