PostgreSQL Interview Q&A Part-3

Share via:

Post Views: 0

PostgreSQL Interview Q&A Part-3

Before going into the article, please check our previous article on PostgreSQL Q&A Part-2

1. Can we have different data directories for PostgreSQL Cluster?

Under One PostgreSQL installation, you can create multiple Clusters. Each Cluster will have its own data directory. One Cluster cannot have multiple data Directories.

Data Directory path would be under the $PGHOME.

2. How do we manage transaction load on PostgreSQL?

PostgreSQL is a delete –insert database. Pre-dominantly used for OLAP loads in Enterprise markets and used as OLTP in SMB’s ( Small & Medium Business).

If you are using Postgres for OLTP, you would need to manage data fragmentation on your high transactional tables. To keep up with the performance, Tables would be needed to be Vacuumed periodically.

Check out the below article for more information on Data Fragmentation:

https://www.ktexperts.com/data-fregmentation-in-postgresql/

3. Can we restrict no. of connections at the database level?

Yes, You can restrict no of connections at the database and the User level.

postgres=# \c db6 postgres
You are now connected to database "db6" as user "postgres".
db6=# \q
[postgres@gg-demo-vm ~]$ psql
psql.bin (10.12)
postgres=# ALTER DATABASE db6 WITH ALLOW_CONNECTIONS false;
ALTER DATABASE
postgres=# \c db6 postgres
FATAL:  database "db6" is not currently accepting connections
Previous connection kept

postgres=# \c db6 postgres

You are now connected to database "db6" as user "postgres".

db6=# \q

[postgres@gg-demo-vm ~]$ psql

psql.bin (10.12)

postgres=# ALTER DATABASE db6 WITH ALLOW_CONNECTIONS false;

ALTER DATABASE

postgres=# \c db6 postgres

FATAL: database "db6" is not currently accepting connections

Previous connection kept

In the above example, we have restricted the connections on Database db6. If you want to revert it back, set the ALLLOW_CONNECTIONS to true.

You can also use the CONNECTION_LIMIT Parameter to limit the connections to the database.

4. Can we do a white list of IP’s to allow the database connection?

Yes, for any remote connection to happen to your database cluster, you would need to make an entry in the pg_hba.conf file.

This file is available under your Data directory. Check the screenshot below for the sample entry.

[postgres@gg-demo-vm data]$ pwd
/dev/PG10/data
[postgres@gg-demo-vm data]$ ls -ltr pg_hba.conf
-rw-------. 1 postgres postgres 4259 Feb 10 04:44 pg_hba.conf
[postgres@gg-demo-vm data]$ cat pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

[postgres@gg-demo-vm data]$ pwd

/dev/PG10/data

[postgres@gg-demo-vm data]$ ls -ltr pg_hba.conf

-rw-------. 1 postgres postgres 4259 Feb 10 04:44 pg_hba.conf

[postgres@gg-demo-vm data]$ cat pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:

host all all 127.0.0.1/32 md5

5. What is the data directories’ ownership after installation?

Since the Installation will happen through the root user, all the directories will be owned by the root. Only the data directory will be owned by Postgres users.

You don’t have to create the Postgres user separately, it will be created along with the installation.

[postgres@gg-demo-vm ~]$ ls -ltr
-rw-r--r--.  1 root     daemon     76223 Feb 11  2020 pgAdmin_3rd_party_licenses.txt
drwxr-xr-x.  8 root     daemon       160 Feb 10 04:43 pgAdmin 4
drwxr-xr-x.  3 root     daemon        60 Feb 10 04:44 server
drwxr-xr-x.  6 root     daemon       120 Feb 10 04:44 stackbuilder
drwxr-xr-x.  4 root     daemon       100 Feb 10 04:44 installer
drwxr-xr-x.  2 root     daemon       720 Feb 10 04:44 bin
drwxr-xr-x.  4 root     daemon      1360 Feb 10 04:44 lib
-rwxr-xr-x.  1 root     daemon       319 Feb 10 04:44 pg_env.sh
drwxr-xr-x.  4 root     daemon       160 Feb 10 04:44 scripts
drwx------. 22 postgres postgres     600 Feb 28 00:00 data

[postgres@gg-demo-vm ~]$ ls -ltr

-rw-r--r--. 1 root daemon 76223 Feb 11 2020 pgAdmin_3rd_party_licenses.txt

drwxr-xr-x. 8 root daemon 160 Feb 10 04:43 pgAdmin 4

drwxr-xr-x. 3 root daemon 60 Feb 10 04:44 server

drwxr-xr-x. 6 root daemon 120 Feb 10 04:44 stackbuilder

drwxr-xr-x. 4 root daemon 100 Feb 10 04:44 installer

drwxr-xr-x. 2 root daemon 720 Feb 10 04:44 bin

drwxr-xr-x. 4 root daemon 1360 Feb 10 04:44 lib

-rwxr-xr-x. 1 root daemon 319 Feb 10 04:44 pg_env.sh

drwxr-xr-x. 4 root daemon 160 Feb 10 04:44 scripts

drwx------. 22 postgres postgres 600 Feb 28 00:00 data

6. How to do remote connectivity to your database server from another server?

You can connect to the database from a remote server using the psql command itself.

Along with the command, you will have to mention the -h <hostname> as the option. Look at the screenshot below:

[postgres@gg-demo-vm ~]$ psql -h localhost -p 5432
psql.bin (10.12)
Type "help" for help.

[postgres@gg-demo-vm ~]$ psql -h localhost -p 5432

psql.bin (10.12)

Type "help" for help.

Here, you have to give IP/Hostname in place of localhost.

7. Can we change/transfer ownership of the database?

Yes. In Postgres, you can change the ownership of the database whenever you want. The only thing that has to be kept in mind is only Superuser has the privileges to do that.

See the below example :

postgres=# \l db6
                             List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
------+----------+----------+-------------+-------------+-------------------
 db6  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# alter database db6 owner to u1;
ALTER DATABASE
postgres=# \l db6
                            List of databases
 Name | Owner | Encoding |   Collate   |    Ctype    | Access privileges
------+-------+----------+-------------+-------------+-------------------
 db6  | u1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# \l db6

List of databases

------+----------+----------+-------------+-------------+-------------------

(1 row)

postgres=# alter database db6 owner to u1;

ALTER DATABASE

postgres=# \l db6

List of databases

------+-------+----------+-------------+-------------+-------------------

db6 | u1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

(1 row)

Here, the owner of the db6 database was a Postgres user. It was changed to u1.

8. List some shortcut commands in PostgreSQL

Postgres is a relational database and understand SQL language. You can also use some of the shortcuts to query the database.

/l , /l+ ( List of databases)
/du, /du+ ( List of Roles)
/dn, /dn+ ( List of Schemas)
/db, /db+ ( List of Tablespaces)

9. Do we have resource configuration at the PostgreSQL level?

There are 2 types of resource configuration in PostgreSQL.

Cluster Level
DB level.

You can make the changes in the PostgreSQL.conf file for the cluster level changes and for the database level parameter changes, you can run them at the database prompt level.

10. Can we create a database with different tablespace?

Yes, by default if you don’t mention anything, pg_default will be the default tablespace for the database.

If you want to create a database with a non-default tablespace, you would need to mention the name of the tablespace at the end of the create database command.

See the eg below :

postgres=# create database db7 tablespace ts2;
CREATE DATABASE
postgres=# \l+ db7
                                                List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
 db7  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7871 kB | ts2        |