Postgresql - Database CreationIt is an organized collection of structured information or data Initdb – Initializes the database clusters. By default, three databases will be created during postgres installation.  Steps to Create Database : 1. List out database :
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Creating database can be of two methods
Syntax :
postgres=# create database tsdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tsdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
3. Steps to connect tsdb database :
postgres=# \c tsdb;
You are now connected to database "tsdb" as user "postgres".
tsdb=#
4. Steps to create schema inside a database : Syntax :
create schema < schema name >;
List out the schemas :
tsdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
tsdb=# create schema s1;
CREATE SCHEMA
tsdb=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
s1 | postgres
(2 rows)
5. Setting the created schema as default schema so that all the objects created from now will be located in this schema :Syntax :
Set search_path to < schema name >;
View the default Schema :
Show search_path;
Changing the schema :
tsdb=# set search_path to s1;
SET
tsdb=# show search_path;
search_path
-------------
s1
(1 row)
6. Steps to drop schema :
tsdb=# drop schema s1;
DROP SCHEMA
tsdb=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
7.Step to drop database :
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tsdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# drop database tsdb;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Step to create database from Physical level :
Listing database
-bash-4.2$ ./psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Check the version
-bash-4.2$ ./psql -c 'select version()';
version
---------------------------------------------------------------------------------------
-------------------
PostgreSQL 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-44), 64-bit
(1 row)
Creating database
-bash-4.2$ ./createdb tsdb
-bash-4.2$ ./psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tsdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
-bash-4.2$ ./psql
psql (13.13)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tsdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
Dropping Database:
-bash-4.2$ ./dropdb tsdb
-bash-4.2$ ./psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
« Previous Next Topic » (Postgresql - Schema Creation) |