In this article, we will see how to find list of active sessions/connections in PostgreSQL database.
We can find list of active/inactive sessions/connections by querying against PostgreSQL system view pg_stat_activity.
Continue reading How to find list of active sessions/connections in PostgreSQL database
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 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:
In this article, we will see how to find currently logged in users in PostgreSQL. We can get the currently logged in users information by querying against pg_stat_activity system view. pg_stat_activity shows the datname, pid, usename, backend_start,wait_event_type,wait_event,state,query,…
SELECT DISTINCT usename FROM pg_stat_activity;
Continue reading How to find currently logged in users in PostgreSQL
In this article, we will see how to check whether a user is connected or not to PostgreSQL server. We can get this information from the system view pg_stat_activity.
select datname from pg_stat_activity where usename='james';
Continue reading How to check whether a user is connected to PostgreSQL or not
In this article, we will see How to get list of users in PostgreSQL. We can get the list of users by running meta command \du and by querying against table pg_user.
1. List of users in PostgreSQL using meta command:
\du
We can create user in PostgreSQL by using createuser program or by using create user statement. To create user in PostgreSQL server, user must have create user(createrole) privileges.
Syntax to Create User in PostgreSQL:
CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime'