Posts Tagged ‘PostgreSQL’

VACUUM vs VACUUM FULL in PostgreSQL

VACUUM VACUUM FULL 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 […]

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 […]

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. Syntax: 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 […]

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 […]

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: pg_dump: 1. This utility is used backing up […]

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 […]

Powered by k2schools