In this article, we will see how to find the name of the current database from within PostgreSQL.
We can get the current database in PostgreSQL by using below commands
select current_database(); \c \conninfo SELECT * FROM current_catalog;
Continue reading How to find the name of the current database from within PostgreSQL?
In this article, we will see how to drop a PostgreSQL database if there are active connections to it?
Below query is used to drop active connections on a database
Syntax for PostgreSQL 9.2 and later:
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB AND pid <> pg_backend_pid();
Syntax for PostgreSQL 9.1 and below 9.1:
Continue reading How to drop a PostgreSQL database if there are active connections to it
In this article, we will see how to disconnect NOLOGIN users from PostgreSQL. In order to make sure that all users whose login privileges has been revoked are disconnected right away.
Query to disconnect NOLOGIN users from PostgreSQL:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity sa JOIN pg_roles r ON sa.usename=r.rolname and not rolcanlogin;
Continue reading How to disconnect NOLOGIN users from PostgreSQL
In this tutorial, we will see How to change owner of database in PostgreSQL. We can change the database owner by using ALTER DATABASE command. If we change the owner name, only database owner name will cnage. But not objects(tables, views,…) owners.
Syntax to change the owner of the database in PostgreSQL: Continue reading How to change owner of database in PostgreSQL
In this article, we will see how to find list of databases and their sizes in PostgreSQL.
Find the list of databases in PostgreSQL Server.
We can find the databases by using:
1. with meta-command
2. By querying against pg_database table.
Continue reading How to find list of databases and their sizes in PostgreSQL
In this article, we will see how to Copy table from one database to another in PostgreSQL. We can copy a table from one database to other using pg_dump tool.
Copy table from one database to another in PostgreSQL:
If table is empty then, run the below command from Linux.
pg_dump -t table_to_copy source_db | psql target_db
Continue reading How to Copy table from one database to another in PostgreSQL
In this article, we will see how to switch role after connecting to database in PostgreSQL. We can switch current connected user/role using set role command.
Switch role after connecting to database in PostgreSQL:
Continue reading How to switch role after connecting to database in PostgreSQL
In this article, we are going to see What is the difference between Vacuum and Vacuum Full.
Vacuum
Plain VACUUM simply reclaims space and makes it available for re-use.
This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained.
However, extra space is not returned to the operating system (in most cases); it’s just kept available for re-use within the same table.
VACUUM FULL
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 form is much slower and requires an exclusive lock on each table while it is being processed.
In this article, we will see how to perform custom format backup and restore of PostgreSQL database.
While plain text format produced by pg_dump is natural and simple, at the same time, it is not very flexible because it can create huge output files.
PostgreSQL has a nice little feature that allows users to export the so-called “custom format”, a format that is archived by default.
Continue reading How to perform custom format backup and restore of PostgreSQL database
In this tutorial, we will see how to take backup and restore a PostgreSQL Database.
It is administrator regular activity to take backup and restore a database from production server to development or UAT servers.
1. pg_dump: Extract a PostgreSQL database into a script file or other archive file.
2. pg_dumpall: Extract a PostgreSQL database cluster into a script file.
3. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
4. pg_restore: restore a PostgreSQL database from an archive file created by pg_dump.
Note: pg_dump,psql, pg_restore and pg_dumpall needs to be executed from Linux shell. Not from psql tool.
On Source PostgreSQL Server:
1. Take backup of database using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Take backup privileges on target server using pg_dumpall
4. Restore the database using psql or pg_restore command.
5. Now, restore the privileges(roles) taken in the step 3.
6. Test the connection to the loaded database.
In this tutorial, i am going to take backup of r2schools from the source PostgreSQL server ‘mongodb1’ and restore the r2schools database into target PostgreSQL server ‘mongodb2’.
1. Create a directory to to store backup files
Create a directory to store backup files. I am creating directory with the name PGBACKUPS in /opt/ path. You create backup directory where you have sufficient space to store backup of database.
postgres@mongodb1:/opt$ mkdir PGBACKUPS postgres@mongodb1:/opt$ cd PGBACKUPS/
2. Now, run the pg_dump command as shown below.
p pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-U postgres–> User name to connect as.
-d r2schools –> Database to take backup.
-W –> Password to be provided for the user.
$(date +%Y-%m-%d_%H_%M_%S) — Current date in YYYY-mm-dd_H_M_S format.
3. Once execute it will ask us the password. Provide the password for the user. Then, backup file created at specified location. In my backup file created at /opt/PGBACKUPS. Lets verify.
postgres@mongodb1:/opt/PGBACKUPS$ pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql Password: postgres@mongodb1:/opt/PGBACKUPS$ ls r2schools2020-01-07_10_39_00.sql postgres@mongodb1:/opt/PGBACKUPS$
If source and target servers are same, then skip this step.
postgres@mongodb1:/opt/PGBACKUPS$ scp -p r2schools2020-01-07_10_39_00.sql root@mongodb2:/home/r2schools/backups/ root@mongodb2's password: r2schools2020-01-07_10_39_00.sql 100% 16KB 7.7MB/s 00:00 postgres@mongodb1:/opt/PGBACKUPS$
Where
r2schools2020-01-07_10_39_00.sql is the backup file.
root@mongodb2:/home/r2schools/backups/ is the username@hostname:target path to copy backup files.
On Target PostgreSQL Server:
Connect to Target server. Verify the backup files moved to successfully or not.
root@mongodb2:/home/r2schools/backups# ls r2schools2020-01-07_10_39_00.sql root@mongodb2:/home/r2schools/backups# pwd /home/r2schools/backups
1. Take backup roles on the target server using pg_dumpall. Here my target server is mongodb2. Execute the below command from Linux host.
pg_dumpall -r > roles$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-r means take only backup of roles.
1. Load the database r2schools on target server using either psql or pg_restore command.
psql -d r2schools < r2schools2020-01-07_10_39_00.sql
Output of above command:
postgres@mongodb2:/home/r2schools/backups$ psql -d r2schools < r2schools2020-01-07_10_39_00.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE SCHEMA ALTER SCHEMA CREATE SCHEMA ALTER SCHEMA CREATE SCHEMA ALTER SCHEMA CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COPY 2 COPY 0 COPY 12 COPY 4 COPY 12 COPY 7 COPY 0 COPY 21 COPY 21 COPY 21 COPY 0 COPY 3 COPY 4 COPY 5 COPY 27 COPY 6 COPY 1 COPY 3 COPY 0 COPY 0 ALTER TABLE ALTER TABLE ALTER TABLE GRANT postgres@mongodb2:/home/r2schools/backups$
psql < roles.sql
\c r2schools \dt
For video tutorial visit below link: