How to Set Timezone in Postgres Databases

Question: How to Set Timezone in Postgres Databases

Answer

Postgres allows you to set a timezone per database. To view the list of available timezones, you may use the following query from psql:

select * from pg_timezone_names;

To set a timezone for say a database called ‘inventory_report’, you may use the following query:

alter database inventory_report set timezone to 'Asia/Dhaka';

Tips:

To List Postgres Databases, you may use the following command from psql:

\l

To List Postgres Tables in a Database:

Connect to the database first:

\c my_prod

Now, list:

\dt

How to Allow Remote User Access in PostgreSQL

Configuration PostgreSQL is a bit tricky and complicated compared to MySQL for a few cases. One such case would be allowing remote user access in PGSQL. You might be interested to allow your home network to connect to your test or production database to run a few queries. To do that, if you are not using a tool like Percona Distribution for Postgres or a Cluster tool like Clustercontrol, it might get a bit complicated.

I have hence, set a list of steps, we need to do to achieve the goal.

Set PostgreSQL to Allow Remote Addresses

To do that, open up your postgresql.conf file. It is usually located under /var/lib/pgsql/9.6/data/postgresql.conf file. I am running PGSQL 9.6, hence the folder is in 9.6. If you are running any other version of PGSQL, it shall change based on the version.

nano /var/lib/pgsql/9.6/data/postgresql.conf

Find the directive ‘listen_address’ and uncomment it, if it is commented. Also, set this to wildcard like the following:

listen_address = "*"

Create a PostgreSQL User with Encrypted Password

Open up your psql console from the user postgres and run the query given below:

su - postgres
psql
create user new_user with encrypted password 'testu1234'

You should always use ‘with encrypted password’, as this would avoid storing your password in plaintext instead of md5.

Now, grant the user on the database, you would like it to access:

grant all privileges on database my_prod to new_user;

Add Remote Access for User in PostgreSQL

PostgreSQL manages a remote access file, to allow access from remote sources. The file is located in /var/lib/pgsql/9.6/data/pg_hba.conf. To allow remote access from an IP, we need to add a line in this file:

Edit the file in nano

nano /var/lib/pgsql/9.6/data/pg_hba.conf

Add the following line

host    all             new_user           27.147.176.2/32       md5

Here 27.147.176.2 is the IP from where I would like to access the database.

Restart PostgreSQL

Now, restart your PostgreSQL instance

systemctl restart postgresql-9.6.service

You should be now set.

Few Additional Things For Reference

  1. How to list the users of PostgreSQL from the console?

    From the Postgres console (psql), run the following command:
    \du
  2. How to change the password for a PostgreSQL user?

From the Postgres console (psql), run the following query:

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

where username is the username you would like the password changed. Remember to use encrypted password keyword to save the password in md5.

3. How to change the user to a superuser?

You may use the following query:

ALTER USER new_user WITH SUPERUSER;

How to Backup/Dump and Restore PostgreSQL Databases

Here is the situation. We have a customer, who runs a POS backed by PostgreSQL. Customers want a second POS with the updated SQL till last night. Point to note, he didn’t want to use the Master/Replica or Master/Master replication, instead, a day earlier backup to be restored.

A similar situation is probably applicable for developers who are trying to work with staging and production environments and vice versa. In such a situation, you need a technique for dumping and restoring databases.

Backup/Dump Postgresql Databases

To dump the PostgreSQL database, you need to use pg_dump. First ssh to your server, change the user to Postgres, and then run the dump

su - postgres
pg_dump your_database > your_database_2021_13_11.sql

Here, your_database is the name of the database you are trying to dump.

Copy the SQL to the Remote Server

Once the dump is done, now you may copy the SQL file to the remote server. You may do that using rsync:

rsync -vrplogDtH --progress your_database_2021_13_11.sql [email protected]:/var/lib/pgsql/

Replace your_database_2021_13_11.sql with your SQL dump name and ‘ip.ip.ip.ip’ with the IP of your remote destination.

Restore the Dump

Now, ssh to the remote server, and continue with the following command:

# change to postgres user
su - postgres
# drop the old database
psql -c 'drop database your_database;'
# create database (replace your_username with the one you want to use for the database)
psql -c 'create database your_database with owner your_username;'
# restore database
psql your_database < /var/lib/pgsql/your_database_2021_13_11.sql

Once done, you should be able to use the new restored database. Good luck.

ERROR: cannot drop the currently open database – PostgreSQL

If you are trying to drop a database and getting an error like the following in PostgreSQL:

ERROR: cannot drop the currently open database

Then, the easiest technique to solve the problem is to restart your PostgreSQL server. I am using PGSQL 9.6, here was my command:

systemctl stop postgresql-9.6.service
systemctl start postgresql-9.6.service

If you are using PostgreSQL 13 or above, you have an option to drop the database with force now. You may drop the database using the following:

DROP DATABASE database_to_drop WITH (FORCE);

Replace ‘database_to_drop’ with the database you would like to drop.

How to aggregate column with non-aggregable value in an Array using Group in PostgreSQL

Let’s imagine, scenario is like the following:

We have three columns with the following values in a table named ‘sale_line’

----------------------------------------
product_id	qty_done	sale_id
----------------------------------------
1010		5.0		101
1010		1.0		102
1010		2.0		103
1012		1.0		104
1012		4.0		105
1012		4.0		106
1012		9.0		107

We have a product_id column that contains which product we are referring to, a qty_done column, which contains the number of products sold, and sale_id refers to the sale order where the product_id was dispatched. Now the example table contains how many of each product dispatched in different sale orders. We can write a query, to group the product_id and the total number of products sold using aggregation

select product_id, sum(qty_done) as total_sold from sale_line group by product_id;

The producing result would be the following:

---------------------------
product_id	total_sold
---------------------------
1010		8.0		
1012		17.0	

We can see, how group by can give you the benefit of aggregation. But have you noticed, we have a column called ‘sale_id’ that doesn’t go with any aggregation function like ‘count’, ‘sum’ or ‘avg’. Can we use them for any purpose?

Sometimes, you may be interested to track down the orders responsible for a set of products using group by when each products will contain the list of orders responsible for causing the total sell. Can we accomplish that in one query?

We actually can. We can concat distinct ids and put them in a resulting array instead of our known aggregation function like ‘count’ or ‘sum’. Postgres provides a array aggregation function called ‘array_agg’, it can be used to produce such result. Have a look at the following query:

select product_id, sum(qty_done) as total_sold, array_agg(sale_id) as sale_ids from sale_line group by product_id;

The producing result would be the following:

-----------------------------------------
product_id	total_sold	sale_ids
-----------------------------------------
1010		8.0		[101, 102, 103]
1012		17.0		[104, 105, 106, 107]

As you can see, we have made postgres return us an array that contains the sale_ids for consisting total_sold even though, sale_id column wasn’t conventionally aggregable or let’s say different than the usual numeric pattern.

This technique can have many use cases. For example if you have a different model/table for managing sale orders, purchase orders, pickings and invoices while, your sale orders contains the price information, and the pickings doesn’t, but picking contains the actual number of products got dispatched, then you may first aggregate the total_sold from the picking table, then produce an average price using the sale_ids you have produced from the picking table using group by and array aggregation, then merge them. This seems quite complicated, I understand, but again, it’s all about putting a break to your reporting time, and manage scalability of your application, putting a constant cost based reporting algorithm using single sql query, even if you process 10 times more orders in future.

How to Display Select Result Vertically in Postgres psql Console

Mysql has a pretty cool feature with \G, that sets the mysql query result in vertical mode. If you have a table with a lot of column, you will face difficulty to display them in horizontal mode. Hence, you want to see the result vertically.

For Postgres, this is called ‘expanded display’. To set the expanded display on or off, you need to use the following command in psql console:

my_prod=# \x
Expanded display is on.
my_prod=# \x
Expanded display is off.
my_prod=#

If your expanded display is on, then \x will set this to off, else it would set the display to on. If you set the expanded display to on, this would show the column and value in a vertical key value pair style display, which is much easier to understand.

How to Backup PostgreSQL Database from Command Line

Question

How to Backup PostgreSQL Database from Command Line

Solution

Postgresql comes with two utilities called ‘pg_dump and ‘pgsql’. These can be used to backup and restore database.

To backup postgresql database using command line, you can use this:

pg_dump database_name > database_name.sql

This would take the backup and store it in .sql file.

To restore postgresql (.sql) file to a database using command line, you can use this:

pgsql database_name &lt; database_name.sql