Tablespace in PostgreSQL

 

Tablespace

In PostgreSQL, tablespaces enable administrators to specify places in the file system where files representing database objects can be kept. When generating database objects, a tablespace can be referred to by name once it has been created.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. 

First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance.

 For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Tablespace Creation 

create tablespace ts1 location '/var/lib/pgsql/13/data/myTS';

Creating Table on a Tablespace

create table i3_newdpt(dpt_id int) tablespace ts1;

PostgreSQL Basic Commands

Basic Command	Description
psql -d database -U user -W	Connects to a database under a specific user
psql -h host -d database -U user -W	Connect to a database that resides on another host
psql -U user -h host “dbname=db sslmode=require”	Use SSL mode for the connection
\c dbname	Switch connection to a new database
\l	List available databases
\dt	List available tables
\d table_name	Describe a table such as a column, type, modifiers of columns, etc.
\dn	List all schemes of the currently connected database
\df	List available functions in the current database
\dv	List available views in the current database
\du	List all users and their assign roles
SELECT version();	Retrieve the current version of PostgreSQL server
\g	Execute the last command again
\s	Display command history
\s filename	Save the command history to a file
\i filename	Execute psql commands from a file
\?	Know all available psql commands
\h	Get help
\e	Edit command in your own editor
\a	Switch from aligned to non-aligned column output
\H	Switch the output to HTML format
\q	Exit psql shell

Start Stop and Status check

systemctl status postgresql-13.service

systemctl stop postgresql-13.service

systemctl start postgresql-13.service

PG Port Number 

select * from pg_settings where name='port';

Create Database

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] db_owner ]

   [ TEMPLATE [=] template ]

   [ ENCODING [=] encoding ]

   [ TABLESPACE [=] tablespace ]

]

Import CSV File Into PostgreSQL

 Create a Table in Postgres 

CREATE TABLE persons (

id SERIAL,

first_name VARCHAR(50),

last_name VARCHAR(50),

dob DATE,

email VARCHAR(255),

PRIMARY KEY (id) );

Create or Import csv file

I am creating here...

vi emp.csv

First Name,Last Name, DOB,Email

Abhishek, Yadav,18-Dec-1982,abhishek.yadav@clotatechnology.com

Deepak, Varma,20-Jan-1982,Deepak.Verma@clotatechnology.com

Connect PostgreSQL and execute below command to import the csv file into persons table

COPY persons(first_name, last_name, dob, email) from '/var/lib/pgsql/emp.csv' DELIMITER ',' CSV HEADER;

pg_restore

     pg_restore restores a PostgreSQL database from an archive file created by pg_dump.

     pg_restore [connection-option...] [option...] [filename]

     Example:

     We have dumped a database called mydb into a custom-format dump file:

     pg_dump -Fc mydb > db.dump

     To drop the database and recreate it from the dump:

     $ dropdb mydb

     $ pg_restore -C -d postgres db.dump

 

      The database named in the -d switch can be any database existing in the  cluster; pg_restore only uses it to issue the CREATE DATABASE command  for mydb. With -C, data is always restored into the database name that appears in the dump file.

   To reload the dump into a new database called newdb:

    $ createdb -T template0 newdb

    $ pg_restore -d newdb db.dump

 

    Notice we don't use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not template1, to ensure it is initially empty.

pg_switch_wal()

pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file.

    pg_switch_wal() returns the end LSN + 1 of the old WAL file.

    However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current WAL file will be returned.

    pg_switch_wal() can only be executed on a primary server (i.e. not a read-only standby).

    postgres=# SELECT pg_switch_wal();

    pg_switch_wal

    ---------------

    0/161E050

     If there has been no activity which generated WAL since the last WAL file  switch, a new WAL file will not be created:

 

    postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());

     pg_walfile_name      |     pg_walfile_name     

--------------------------+--------------------------

 000000010000000200000086 | 000000010000000200000086

(1 

PG Backup Dump

Types of DB backup

·         Logical BACKUP  <pg_dump>

·         Physical Backup 

PG SQL Dump

    Three tasks we are going to do... 

       Restoring a backup

    Performing a backup

    Scheduling a backup job to run daily at 1am

Create acweb Database

createdb acweb

Download TestDB from gitHub

wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar

Restore the downloaded DB

-bash-4.2$ pg_restore --dbname=acweb --verbose acweb.tar

Check Tables…

acweb=# \dt *.*

                        List of relations

       Schema       |          Name           | Type  |  Owner

--------------------+-------------------------+-------+----------

 hr                 | staff                   | table | postgres

Download a script to create our access log table in the dev database:

wget https://raw.githubusercontent.com/linuxacademy/content-postgresql-deepdive/master/acweb/accessLog.sql

Now execute the script on the DB

psql -f accessLog.sql acweb

Create a DIR for backup…

mkdir backups

 pg_dump -F t acweb > backups/acwebdev.tar

 Schedule a Backup

Download this backup script:

wget https://raw.githubusercontent.com/linuxacademy/content-postgresql-deepdive/master/acweb/pg_backup.sh

 

Make the script executable:

chmod +x pg_backup.sh

Test the script by executing it:

./pg_backup.sh

Edit Edit crontab: to run the backup at 1 AM daily.

crontab –e

0 1 * * * /var/lib/pgsql/pg_backup.sh

 crontab -l

0 1 * * * /var/lib/pgsql/pg_backup.sh

For backup PostgreSQL databases using the pg_dump and pg_dumpall tool.

Type of backups:

• Full / partial databases
• Both data and structures, or only structures
• Point-in-time recovery
• Restore performance

 pg_dump -U username -W -F t database_name > \u05\backup\backup_file.tar

-F : specifies the output file format that can be one of the following:

• c: custom-format archive file format
• d: directory-format archive
• t: tar
• p: plain-text SQL script file).

 pg_dumpall program exports all databases, one after another, into a single script file.

pg_dumpall -U postgres > \u05\pgbackup\all.sql

Backup database object definitions

pg_dumpall --schema-only > \u05\pgdump\definitiononly.sql

pg_dumpall --roles-only > \u05\pgdump\allroles.sql

pg_dumpall --tablespaces-only > \u05\pgdump\allroles.sql

Restore Command

Note: We need to create DB manually.  

psql <db_name> <<Fine_Name.sql>>

psql db_name <  backup_dump_file.sql

Physical Backup

 

For Backup

Step 1. Enable WAL (For PITR)

                Created /var/lib/pgsql/11/archive_wal

                Restarted DB instance.

/*

#------------------------------------------------------------------------------

# WRITE-AHEAD LOG

#------------------------------------------------------------------------------

# - Settings -

#wal_level = replica                    # minimal, replica, or logical*/

Step 2. Take backup (Full)

 

 

/var/lib/pgsql/backup/pg_db_bkp

 

pg_basebackup -Ft -D /var/lib/pgsql/backup/pg_db_bkp -P

 

{Don’t use it} pg_basebackup --pgdata=/var/lib/pgsql/11/replicated_data -P

op=> 373575/373575 kB (100%), 1/1 tablespace

-D directory

-F format

t tar

-z gzip

-P progress

  

For Restore

                Step 1. Stop DB Server

                                                systemctl stop postgresql-11

                Step 2. Delete all the data

cd /var/lib/pgsql/11/data/

rm -rf *

                                               

Step 3. Extract tar files

                                tar xvf /var/lib/pgsql/backup/pg_db_bkp/base.tar -C /var/lib/pgsql/11/data/

                                tar xvf /var/lib/pgsql/backup/pg_db_bkp/pg_wal.tar -C /var/lib/pgsql/11/data/pg_wal/

 

Step 4. Add recovery.conf

vi /var/lib/pgsql/11/data/recovery.conf

 

restore_command = 'cp /var/lib/pgsql/11/archive_wal/%f "%p"'

recovery_target_time = '2020-09-14 16:02:00 UTC'

recovery_target_name = 'flag-1'

recovery_target_inclusive = false

 

/*Notes

·         recovery_target -> recovery should end as soon as a consistent state is reached

·         recovery_target_name -> specifies the named restore point (created with pg_create_restore_point()) to which recovery will proceed.

sep7_2020=# select pg_create_restore_point('flag_1');

 pg_create_restore_point

-------------------------

 0/690001A8

(1 row)

·         recovery_target_time -> specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive.

recovery_target_inclusive (boolean)

Specifies whether to stop just after the specified recovery target (true), or just before the recovery target (false).

 */

  

Start DB Instance