In the below tutorial , we will explain how to clone a  POSTGRES database to remote server. source postgres details: host      -> dbaclass-local db_name   -> dbatest — > This database need to be migrated. port      -> 5444 superuser -> enterprisedb remote postgres server details: host       -> dbaclass-remote port    […]

Creating a database in postgres is very easy and it takes just few seconds. create database command is used to create the database.In this tutorial we will show different option with create database command.   NOTE – > When we install and initialise postgres cluster, by default two template database will be created, one is […]

File_fdw is an extension which can be used to access flat files like csv in the server’s file system. Lets see the below DEMO: 1. Check whether file_fdw extension is available on server or not: edb=# select * from pg_available_extensions where name=’file_fdw’; name | default_version | installed_version | comment ———-+—————–+——————-+——————————————- file_fdw | 1.0 | 1.0 […]

In postgres you can make a particular database or all databases of a postgres cluster. default_transaction_read_only parameter controls whether database is in read only or read write . default_transaction_read_only -> on ( for read only ) default_transaction_read_only -> off ( for read write ) For making single db read only.( DB_NAME – > edbstore) –connect […]

Below are the steps for enabling archive mode or wal archiving in postgres. STEPS TO ENABLE ARCHIVE MODE IN POSTGRES: 1. Check archive setting in the postgres config file: postgres=# select name,setting from pg_settings where name like ‘archive%’ ; name | setting ————————-+———— archive_cleanup_command | archive_command | (disabled) archive_mode | off — >>> archive_timeout | […]

Below are the steps for moving a tablespace to a new file system/mount point in postgres. EXAMPLE: TABLESPACE_NAME – > proddb_ts TABLESPACE_OID – 19847 NEW MOUNT POINT – > /new_fs/data 1. Get the tablespace details and existing path: postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ——-+————+———-+——————————————————+———— 1663 | […]

PROBLEM: We have granted select privilege on one table of a schema to another user. Even after that the user was getting permission denied error while selecting data. — Privilege was granted like this PRIMDB=# grant all privileges on table SCOTT.SERVER_LOAD_INFO to prim_user; GRANT Now connect to prim_user and run the select statement: psql -d […]

There are multiple ways to drop a database in postgres . DEMO: In this article, we will drop the database named: DBACLASS. postgres=# \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ———–+———-+———-+———+——-+———————– DBACLASS | postgres | UTF8 | C | C | — >>>>>>> THIS ONE postgres […]

In this tutorial, i have explained steps for installing EDB postgres database on macos. The steps are similar for linux system also. It will be a GUI method.   Download the software:   Link – > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads   Download the executable according to your platform.         2. Start the downloaded executable:     Now […]