VACUUM simply reclaims space and makes it available for re-use. However, extra space is not returned to the operating system. It’s just kept available for re-us.e within the same table VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system
This is faster This is slower
Exlcusive lock not required VACUUM Full requires exclusive on each tables while it is being processed

Rename user in PostgreSQL

In this article, we are going to rename user with new name. To rename user, user must have alter user permission or must be superuser.We can not rename the current session user. If we want to rename the current session user, then we need to logout and then login as a different user to run the ALTER USER statement.



How to check table exists or not in PostgreSQL database

We can check whether table exists or not using information_schema.tables,pg_catalog.pg_class, pg_tables, using meta command or to_regclass.

In this tutorials, we are going to check table named ‘accounts’ exists or not in ‘bank’ database.

1) Using information_schema.tables.


SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name = 'table_name' );

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Reason: pg_dump by default creates the sql commands necessary to recreate the data. To recover it, you just need to invoke psql (not pg_restore ) with the file as input . pg_restore is only to be used for the binary format of pg_dump.

Resolution: Use -Fc option with pg_dump. Then, only we will be able to restore database with pg_restore.

pg_dump: [archiver] could not open output file: Permission denied

Following error occurs when try to take backup of database to the file system. But, the user(superuser) does not have permission to backup location.

pg_dump: [archiver] could not open output file "bank_20170531.sql": Permission denied.

Backup Command tried to run:

pg_dump -U postgres bank -f /home/vijay/dumps/bank_20170531.sql

Resolution: Change permission of directory /home/vijay/dumps/ to user postgres.

chown -R postgres:postgres /home/vijay/dumps/

Backup and Restore Database in PostgreSQL

There are different ways to take backup and restore database in PostrgeSQL. With PostgreSQL, backups can be full, incremental or continuous, and they can be at logical or filesystem level. Point-in-time recovery is also possible from incremental backups.

Tools Used for Backup and Restore Database in PostgreSQL:

1. This utility is used backing up a database. pg_dump does not block other users accessing database.
2.pg_dump can create dumps as plain SQL files, as a tar archive, as a directory with a set of files or as a single file called a custom format file.
3.pg_dump does not does not dump global objects such as roles(users and groups) and tablespaces. These are only dumped by pg_dumpall utility.


PostgreSQL DBA Training

PostgreSQL DBA training

1) PostgreSQL installation

  • Installation On Ubuntu
  • Installation On Windows
  • Connecting PostgreSQL Server
  • Start and stop PostgreSQL Server
  • When Started?
  • Default databases installed with PostgreSQL Server

How to start PostgreSQL Server in single user mode

Why single user mode?

Any current connections to the database are dropped without warning
One and only one connection is then allowed into that database
The CHECKPOINT process is not started

How to start PostgreSQL Server in single user mode?

Largest databases in PostgreSQL

In this tutorial, we will sort the databases by its size. Then, we will get top 5 largest databases of PostgreSQL server.

1. To get the databases in sorted order by its size(desc).

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc;

Get largest table sizes in PostgreSQL

In this tutorial, we are going to get top 5 largest table in PostgreSQL and sort the tables of database by size in descending order.

Top 5 largest tables in descending order:

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC limit 5;

Powered by k2schools