Feroz DBA'S Blog

  Step-1: Validate the archive mode status  postgres=# show archive_mode;  archive_mode --------------  off (1 row) postgres=# show archive_command;  archive_command -----------------  (disabled) (1 row) Step-2: Create the archive location  mkdir -p /var/lib/pgsql/14/archive Step3: Enable archive mode  postgres=# ALTER SYSTEM SET archive_mode to 'ON'; ALTER SYSTEM postgres=# ALTER SYSTEM SET archive_command TO 'cp %p /var/lib/pgsql/14/archive/archive%f'; ALTER SYSTEM postgres=# show archive_mode;  archive_mode --------------  off (1 row) postgres=# show archive_command;  archive_command -----------------  (disabled) (1 row) Step4: Set the standard parameter for better performance postgres=# alter system set wal_keep_size='500MB'; ALTER SYSTEM postgres=# alter system set archive_timeout='900s'; ALTER SYSTEM postgres=# alter system set max_wal_senders=10; archive_timeout: Forces a WAL switch after a predetermined period of time a...

  Tablesapce Basics What is a tablespace? It defines where the contents of the tables should be saved. Rules: We can't use a database unless we have tablespace. pg_default is the default tablespace for all the databases. We can define the non-default tablespace while creating a database or can alter the existing database/s by assigning a non-default tablespace. Users can define on which tablespace the objects should be stored when creating them. What are the different types of tablespaces present in the Postgres database? There are two different types of tablespace: Default: by default, two tablespaces will be created at the server level. pg_default and pg_global Non-default: User created manually.  postgres=# select * from pg_tablespace;   oid  |  spcname   | spcowner | spcacl | spcoptions -------+------------+----------+--------+------------   1663 | pg_default |       10 |        |   1664 | pg_glob...