About of Database Systems and Information Technologies
With PostgreSQL 15 version, logical replication became applicable by filtering on the tables by column and row. In this way, replication can be made in the desired columns and rows in the tables. Logical Replication wal_level=logical For logical replication, the wal_level parameter must be logical in the postgresql.conf file . By default, the value of wal_level is replica . The replica value provides the necessary wal files for streaming…
PostgreSQL version 15 was released and made available on October 13, 2022. PostgreSQL, the most advanced open source database, brought many new features with its 15th version. PostgreSQL 15 New Features Merge Command With the release of PostgreSQL 15, the Merge command was also made available. MERGE allows you to write a conditional SQL query that…
pg_rewind is a PostgreSQL tool since 9.5 version. It provides synchronization between standby and old primary. Especially, pg_rewind provides advantage for big databases. You can sync databases without using pg_basebackup. pg_rewind scans the old primary database’s pgdata folder and identifies data blocks changed during the switch to standby database, then copies only changed blocks from…
pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4. Major PostgreSQL…
List, Add, Drop Database List databases postgres=# \d Add database postgres=# createdb testdb or postgres=# create database testdb with owner facar; Drop database postgres=# dropdb testdb List, Add, Drop, Grant User and Schema List Users postgres=# select * from pg_user; Add User postgres=# create user facar with encrypted password ‘password’; or postgres=# createuser (interactive user create)…
Error When you execute drop database command, database get an error like ERROR: database “database_name” is being accessed by other users.> Solution
When you use psql command prompt, system automaticly saves your prompts. If you use password when user creates operation, this situation can occur security risk. Therefore, you can delete this prompts with editing psql_history file like below. Find And Delete psql Prompts [root@pg ~]# locate .psql_history /var/lib/pgsql/.psql_history [root@pg ~]# vi /var/lib/pgsql/.psql_history
You can take schedule backup with shell script. Firstly, You must create shell script file in operating system. After you can append to crontab. Crontab is a UNIX command that creates a table or list of commands, each of which is to be executed by the operating system at a specified time. crontab is used…
You can use two statement for data dump. These statements are pg_dump and pg_dumpall. pg_dump can has table data, but pg_dumpall can has all database objects these are users, groups, stores and other object, so pg_dumpall has all database object. You can move database from product system to test system with pg_dumpall easyly. Example For…