Reload "pg_hba.conf" in self-managed PostgreSQL

 

Modify the $PGDATA/pg_hba.conf, and query to verify the content

Alternatively, use SELECT pg_read_file('pg_hba.conf') to query the contents.

postgres=# select * from pg_hba_file_rules;
-[ RECORD 1 ]----------------------------------------
line_number | 85
type        | local
database    | {all}
user_name   | {all}
address     |
netmask     |
auth_method | peer
options     |
error       |
-[ RECORD 2 ]----------------------------------------
line_number | 87
type        | host
database    | {all}
user_name   | {all}
address     | 127.0.0.1
netmask     | 255.255.255.255
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 3 ]----------------------------------------
line_number | 88
type        | host
database    | {all}
user_name   | {all}
address     | 0.0.0.0
netmask     | 0.0.0.0
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 4 ]----------------------------------------
line_number | 90
type        | host
database    | {all}
user_name   | {all}
address     | ::1
netmask     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 5 ]----------------------------------------
line_number | 93
type        | local
database    | {replication}
user_name   | {all}
address     |
netmask     |
auth_method | peer
options     |
error       |
-[ RECORD 6 ]----------------------------------------
line_number | 94
type        | host
database    | {replication}
user_name   | {all}
address     | 127.0.0.1
netmask     | 255.255.255.255
auth_method | scram-sha-256
options     |
error       |
-[ RECORD 7 ]----------------------------------------
line_number | 95
type        | host
database    | {replication}
user_name   | {all}
address     | ::1
netmask     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
auth_method | scram-sha-256
options     |
error       |

Check the time pg_bha.conf modification time vs load time.

postgres=# SELECT pg_conf_load_time(), modification,current_timestamp FROM pg_stat_file('pg_hba.conf');
-[ RECORD 1 ]-----+------------------------------
pg_conf_load_time | 2022-08-28 11:19:29.742823+00
modification      | 2022-08-28 11:40:19+00
current_timestamp | 2022-08-28 11:52:26.818239+00

Reload the configuration

postgres=# SELECT pg_reload_conf();
-[ RECORD 1 ]--+--
pg_reload_conf | t

Check the time pg_bha.conf modification time vs load time.

postgres=# SELECT pg_conf_load_time(), modification,current_timestamp FROM pg_stat_file('pg_hba.conf');
-[ RECORD 1 ]-----+------------------------------
pg_conf_load_time | 2022-08-28 11:54:03.659547+00
modification      | 2022-08-28 11:40:19+00
current_timestamp | 2022-08-28 11:54:03.659549+00

Examples to hstore in PostgreSQL

 

Testing environment

test=# select version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)

Install extension(s)

test=# create extension hstore;
CREATE EXTENSION

test=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 hstore  | 1.8     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Create table

test=# create table hstore_data (data hstore);
CREATE TABLE

test=# \dt+ hstore_data
                                        List of relations
 Schema |    Name     | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+-------------+-------+----------+-------------+---------------+------------+-------------
 public | hstore_data | table | postgres | permanent   | heap          | 8192 bytes |
(1 row)

Insert record(s)

test=# insert into hstore_data values('"key1"=>"val1",key2=>val2,"key3 with space"=>val3,key4=>NULL');
INSERT 0 1

Query record(s)

select data['key1'] as key1,
       data['key2'] as key2,
       data['key3 with space'] as key3,
       data['key4'] as key4
from hstore_data;

test=# select data['key1'] as key1,
test-#        data['key2'] as key2,
test-#        data['key3 with space'] as key3,
test-#        data['key4'] as key4
test-# from hstore_data;
 key1 | key2 | key3 | key4
------+------+------+------
 val1 | val2 | val3 |
(1 row)

Update record(s)

test=# update hstore_data set data['key1']='val1a';
UPDATE 1

Query record(s)

test=# select data['key1'] as key1,
       data['key2'] as key2,
       data['key3 with space'] as key3,
       data['key4'] as key4
from hstore_data;
 key1  | key2 | key3 | key4
-------+------+------+------
 val1a | val2 | val3 |
(1 row)

Reference: https://www.postgresql.org/docs/current/hstore.html

How to clear unarchived redo log file? (fix redo log member missing issue)

Symptom:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/db/ORCL/onlinelog/o1_mf_1_k4fz1yon_.log
/oradata/db/ORCL/onlinelog/o1_mf_2_k4fz1y42_.log
/oradata/db/ORCL/onlinelog/o1_mf_3_k4fz1xll_.log
/oradata/db/ORCL/onlinelog/o1_mf_4_k4fz1zn7_.log

SQL> select group#,status from v$Log;

    GROUP# STATUS
---------- -------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 INACTIVE

SQL> ! ls -l /oradata/db/ORCL/onlinelog/
total 262152
-rw-r----- 1 oracle oinstall 134218240 Aug 19 22:00 o1_mf_2_k4fz1y42_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 03:22 o1_mf_4_k4fz1zn7_.log

Steps to fix:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance ORCL (thread 1) needs to be archived
ORA-00312: online log 1 thread 1:
'/oradata/db/ORCL/onlinelog/o1_mf_1_k4fz1yon_.log'

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> ! ls -l /oradata/db/ORCL/onlinelog/
total 524308
-rw-rw---- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_1_kj07to4t_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_2_k4fz1y42_.log
-rw-rw---- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_3_kj07v76f_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 03:22 o1_mf_4_k4fz1zn7_.log