what are _fsm and _vm files in postgres?

_fsm and _vm files will be not calculated while checking the table size.

_fsm :
                free space map file :how face free space pages available for usage.

_vm :   
               visibility map: what are the pages already occupied. excluding dead tuples.


e.g.,

$ cd <pg_base_dir/database_id>
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 2608_vm
-rw-------. 1 postgres postgres  24576 Apr 19 18:59 2608_fsm
-rw-------. 1 postgres postgres 442368 Apr 19 18:59 2608
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 1259_vm
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 2619_vm
-rw-------. 1 postgres postgres  24576 Apr 19 18:59 2619_fsm

......

Query to find parameters which requires restart in postgresql

Ans:

select name,setting from pg_settings where context='postmaster';

e.g.,

postgres=#  select name,setting from pg_settings where context='postmaster';
                name                 |                 setting                 
-------------------------------------+-----------------------------------------
 allow_system_table_mods             | off
 archive_mode                        | off
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 bonjour                             | off

.....

Fixing psql version issue in postgres

-- Fixing psql version issue
-- Higher postgres version commands will not work even postgres cluster is higher version and your psql version is lower version

Example: \ds commands will not work psql 8

case : \ds command not worked even after using postgres 10.12

example:

postgres=# \db
ERROR:  column "spclocation" does not exist
LINE 3:   spclocation AS "Location"

-- Find postgres cluster version
          
postgres=# select version();
                               version                                                  
--------------------------------------------------------------------
 PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

postgres=# select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 pg_demo_ts
(3 rows)

postgres=# \db
ERROR:  column "spclocation" does not exist
LINE 3:   spclocation AS "Location"
          ^
postgres=# \q

-- See your psql version

bash-4.1$ psql postgres
Password: 
psql (8.4.18, server 10.12)
WARNING: psql version 8.4, server version 10.12.
         Some psql features might not work.
Type "help" for help.

postgres-# \q
bash-4.1$ 

Let us fix: ( Note: you use your postgres binary location)
Find pg_env.sh file

bash-4.1$ cd /opt/PostgreSQL/10

bash-4.1$ ls pg_env.sh
pg_env.sh  

Run below shell script:

bash-4.1$ source pg_env.sh

bash-4.1$ psql postgres
Password: 
psql.bin (10.12)
Type "help" for help.

Now run the command, it will work.

postgres=# \db
             List of tablespaces
    Name    |  Owner   |       Location       
------------+----------+----------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 pg_demo_ts | postgres | /home/demo_base
(3 rows)

postgres=# 

How to use pgbadger in open source postgres and generate pgbadger report

Sometimes logs are the last things checked when things are going wrong, but they are usually the first things screaming for help when something happens. Manually looking through the logs for problems helps, but why not use log analyzers to automatically generate reports to provide insight on the database before something goes wrong?

The PostgreSQL log analyzer “pg-badger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file. The report it generates shows all information found in a nice and easy to read report format. These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system.

Demonstration: How to generate pg-badger report:

Note: Here we are not analyze the report.

-- Let us create a new PostgreSQL cluster and then we ll create new databases, users and add some load

-- Create a base directory

# cd /opt/PostgreSQL/10/

# mkdir pgdata

[root@postgres 10]# chown postgres:postgres pgdata -R

-- Initialize the base directory 

[root@postgres 10]# su - postgres
$ pwd
/opt/PostgreSQL/10

$ initdb -D pgdata
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
....
....
Success. You can now start the database server using:

    pg_ctl -D pgdata -l logfile start
$

$ 

-- Now change the port number to access the new cluster change given below log settings

$ cd pgdata/
$ ls postgresql.conf 
postgresql.conf


$ vi postgresql.conf

port=5005

log_destination = 'stderr'
logging_collector = on
log_rotation_age = 0
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_lock_waits = on
log_statement = 'all'
log_temp_files = 0

:wq

-- Now start the cluster

pg_ctl -D /opt/PostgreSQL/10/pgdata start

$ pg_ctl -D /opt/PostgreSQL/10/pgdata start
waiting for server to start....2020-04-19 22:19:33 IST [17280]: [1-1] user=,db= LOG:  00000: listening on IPv6 address "::1", port 5005
2020-04-19 22:19:33 IST [17280]: [2-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:593
2020-04-19 22:19:33 IST [17280]: [3-1] user=,db= LOG:  00000: listening on IPv4 address "127.0.0.1", port 5005
2020-04-19 22:19:33 IST [17280]: [4-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:593
2020-04-19 22:19:33 IST [17280]: [5-1] user=,db= LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.5005"
2020-04-19 22:19:33 IST [17280]: [6-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:587
2020-04-19 22:19:33 IST [17280]: [7-1] user=,db= LOG:  00000: redirecting log output to logging collector process
2020-04-19 22:19:33 IST [17280]: [8-1] user=,db= HINT:  Future log output will appear in directory "log".
2020-04-19 22:19:33 IST [17280]: [9-1] user=,db= LOCATION:  SysLogger_Start, syslogger.c:666
 done
server started
$ 


-- Connect to the newly created cluster

$ psql -p 5005 postgres
psql.bin (10.9)
Type "help" for help.

-- From here now create some users, create databases and cone=nect to these databases and create some load
Example:

postgres=# create user user1 password 'Pas$w0rd';
CREATE ROLE
postgres=# create user hr password 'Pas$w0rd';
CREATE ROLE
postgres=# create user admin password 'Pas$w0rd';
CREATE ROLE
postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE
postgres=# create database db3;
CREATE DATABASE
postgres=# \c db1 user1
You are now connected to database "db1" as user "user1".
db1=> create table tbl_test as select * from pg_class;
SELECT 341
db1=> select count(*) from tbl_test;
 count 
-------
   341
(1 row)

db1=> \c db2 hr
You are now connected to database "db2" as user "hr".
db2=> create table tbl_test1 (id int);
CREATE TABLE
db2=> insert into tbl_test1 select * from generate_Series(1,100000) order by random();
INSERT 0 100000
db2=> update tbl_test set id=1 where id between 1 and 100;
UPDATE 100
db2=> 
db2=> \c db3 admin
You are now connected to database "db3" as user "admin".
db3=> 
db3=> 
db3=> create table tbl_test2 (id int,name varchar);
CREATE TABLE
db3=> 
db3=> 
db3=> insert into tbl_test2 values(generate_series(1,100000),'data '||generate_series(1,100000));
INSERT 0 100000

db3=> 
db3=> 
db3=> delete from tbl_test2 where id between 1 and 6000;
DELETE 6000
db3=> 
db3=> 
db3=> select count(*) from tbl_test2;
 count 
-------
 94000
(1 row)

db3=> \q

-- Now download the pg-badger and copy to some location

https://sourceforge.net/projects/pgbadger/

Example:
#
[root@postgres Postgres Software]# cp pgbadger-master.zip /opt
[root@postgres Postgres Software]# cd /opt


[root@postgres opt]# unzip pgbadger-master.zip 

[root@postgres opt]# cd /opt/pgbadger-master
[root@postgres pgbadger-master]# pwd
/opt/pgbadger-master
[root@postgres log]# /opt/PostgreSQL/10/pgdata/log
[root@postgres log]# ls
postgresql-2020-04-19_221933.log
[root@postgres log]# /opt/pgbadger-master
[root@postgres pgbadger-master]# 
[root@postgres pgbadger-master]# ./pgbadger -f stderr -o report.html /opt/PostgreSQL/10/pgdata/log/postgresql-2020-04-19_221933.log 

e.g.,

[root@postgres pgbadger-master]# ./pgbadger -f stderr -o report.html /opt/PostgreSQL/10/pgdata/log/postgresql-2020-04-19_221933.log
[========================>] Parsed 15468 bytes of 15468 (100.00%), queries: 28, events: 3
LOG: Ok, generating html report...
[root@postgres pgbadger-master]# 

[root@postgres pgbadger-master]# ls -ltr
total 2148
drwxr-xr-x. 2 root root    4096 Apr 18  2016 tools
-rw-r--r--. 1 root root   30433 Apr 18  2016 README
-rwxr-xr-x. 1 root root 1219877 Apr 18  2016 pgbadger
-rw-r--r--. 1 root root     334 Apr 18  2016 META.yml
-rw-r--r--. 1 root root      81 Apr 18  2016 MANIFEST
-rw-r--r--. 1 root root    1400 Apr 18  2016 Makefile.PL
-rw-r--r--. 1 root root     903 Apr 18  2016 LICENSE
drwxr-xr-x. 2 root root    4096 Apr 18  2016 doc
-rw-r--r--. 1 root root     347 Apr 18  2016 CONTRIBUTING.md
-rw-r--r--. 1 root root   81420 Apr 18  2016 ChangeLog
-rw-r--r--. 1 root root  832859 Apr 19 22:41 report.html

Now download report.html to your windows machine and analyze it.


Here is one sample screen-shot:

create custom template database and create new database using custom template database in postgres

Here you can see one demo on below requirement:

create custom template database and create new database using custom template database in postgres

-- connect to to your database

$ psql postgres

postgres# \c template1

template1=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
(2 rows)

-- Note: you can't use template0 database
e.g.,

template1=# \c template0 postgres
FATAL:  database "template0" is not currently accepting connections
Previous connection kept
template1=# 

-- create new database e.g., template2

template1=# create database template2;
CREATE DATABASE
template1=# 
template1=# \c template2 postgres
You are now connected to database "template2" as user "postgres".
template2=# 

-- Create required objects in this new database
e.g.,
template2=# create table tblx as select * from pg_class;
SELECT 341
template2=# 
template2=# create table tblz as select * from pg_description;
SELECT 4560
template2=# 
template2=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

-- Now convert this database as a template database

template2=# update pg_database set datistemplate=true where datname='template2';
UPDATE 1
template2=# 
template2=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
 template2
(3 rows)

-- Now you can create new custom database using custom template database e.g., template2

template2=# create database testdb template template2;
CREATE DATABASE
template2=# 
template2=# \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# 
testdb=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

testdb=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | tblx | table | postgres | 80 kB  | 
 public | tblz | table | postgres | 320 kB | 
(2 rows)