User defined database creation in postgresql ~ Database Administrator Tutorial

Database creation in PostgreSQL

In PostgreSQL there are different methods to install the database like using yum install we can create database in default location and other one is user defined method database creation.

The disadvantage with default method is, software and database will be created under the /usr and /var/lib and those mount points not contain much space.

Below User defined database creation in postgresql have more control for administrators to manage.

Follow below steps to create database in PostgreSQL.

Step1 : Install software using below yum repository

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server

Step 2 : Create a Directory where mount point having sufficient space and copy bash profile to the newly created directoy.

[root@server1 ~]# mkdir /app01
[root@server1 ~]# cp /root/.bash_profile /app01/

Step 3 : Open the bash profile under newly created directory and specify the PostgreSQL binaries path.

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH=/usr/pgsql-14/bin:$PATH
export PATH

Step 4 : Change ownership for binaries and database directories.

[root@server1 ~]# chown -R postgres:postgres /app01/

[root@server1 ~]# chown -R postgres:postgres /usr/pgsql-14/

Step 5 : Open the password file under /etc location and change user home location.

vi /etc/password
postgres:x:1001:1001:PostgreSQL:/app01:/bin/bash

Step 6 : Now, login as postgres user and verify bashprofile and psql connectivity and version.

[root@server1 ~]# su - postgres

-bash-4.2$ pwd

/app01

-bash-4.2$ initdb --version

initdb (PostgreSQL) 14.11

Step 7 : Create a Directory under /app01/ for new cluser database and create database using initdb utility.

-bash-4.2$ mkdir cluster1

-bash-4.2$ initdb -D cluster1/

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory cluster1 ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default time zone ... Asia/Kolkata

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D cluster1/ -l logfile start

-bash-4.2$ ls -ltr

total 4

drwx------. 19 postgres postgres 4096 Feb 15 13:38 cluster1

-bash-4.2$ pwd

/app01

-bash-4.2$ cd cluster1/

-bash-4.2$ ls -ltr

total 56

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_twophase

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_snapshots

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_serial

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_notify

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_dynshmem

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_commit_ts

-rw-------. 1 postgres postgres 3 Feb 15 13:38 PG_VERSION

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_tblspc

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_stat_tmp

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_stat

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_replslot

drwx------. 4 postgres postgres 36 Feb 15 13:38 pg_multixact

-rw-------. 1 postgres postgres 28764 Feb 15 13:38 postgresql.conf

-rw-------. 1 postgres postgres 88 Feb 15 13:38 postgresql.auto.conf

-rw-------. 1 postgres postgres 1636 Feb 15 13:38 pg_ident.conf

-rw-------. 1 postgres postgres 4789 Feb 15 13:38 pg_hba.conf

drwx------. 2 postgres postgres 18 Feb 15 13:38 pg_xact

drwx------. 3 postgres postgres 60 Feb 15 13:38 pg_wal

drwx------. 2 postgres postgres 18 Feb 15 13:38 pg_subtrans

drwx------. 2 postgres postgres 4096 Feb 15 13:38 global

drwx------. 5 postgres postgres 41 Feb 15 13:38 base

drwx------. 4 postgres postgres 68 Feb 15 13:38 pg_logical

Step 8 : If default PORT number want change, open postgresql configuration file and change the post number as like below and save the file.

-bash-4.2$ vi postgresql.conf

port = 2221 # (change requires restart)

Step 9 : Start the database using pg_ctl.

-bash-4.2$ ps -ef | grep postgre

root 4717 2248 0 13:36 pts/0 00:00:00 su - postgres

postgres 4718 4717 0 13:36 pts/0 00:00:00 -bash

postgres 4832 4718 0 13:41 pts/0 00:00:00 ps -ef

postgres 4833 4718 0 13:41 pts/0 00:00:00 grep --color=auto postgre

-bash-4.2$ pg_ctl -D /app01/cluster1/ start

waiting for server to start....2024-02-15 13:42:14.812 IST [4849] LOG: redirecting log output to logging collector process

2024-02-15 13:42:14.812 IST [4849] HINT: Future log output will appear in directory "log".

done

server started

-bash-4.2$ ps -ef | grep postgres

root 4717 2248 0 13:36 pts/0 00:00:00 su - postgres

postgres 4718 4717 0 13:36 pts/0 00:00:00 -bash

postgres 4849 1 0 13:42 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /app01/cluster1 >> Postmaster - Main supervisor process in postgresql

postgres 4850 4849 0 13:42 ? 00:00:00 postgres: logger

postgres 4852 4849 0 13:42 ? 00:00:00 postgres: checkpointer

postgres 4853 4849 0 13:42 ? 00:00:00 postgres: background writer

postgres 4854 4849 0 13:42 ? 00:00:00 postgres: walwriter

postgres 4855 4849 0 13:42 ? 00:00:00 postgres: autovacuum launcher

postgres 4856 4849 0 13:42 ? 00:00:00 postgres: stats collector

postgres 4857 4849 0 13:42 ? 00:00:00 postgres: logical replication launcher

postgres 4866 4718 99 13:42 pts/0 00:00:00 ps -ef

postgres 4867 4718 0 13:42 pts/0 00:00:00 grep --color=auto postgres

Step 10 : Login into the database and using postgres user and verify all the file.

-bash-4.2$ psql -U postgres -p 2221

psql (14.11)

Type "help" for help.

postgres=# select datname from pg_database;

datname

-----------

postgres

template1

template0

(3 rows)

postgres=# \l+

List of databases

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

(3 rows)

postgres=# \db+

List of tablespaces

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

(2 rows)

postgres=# select * from pg_tablespace;

oid | spcname | spcowner | spcacl | spcoptions

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

1663 | pg_default | 10 | |

1664 | pg_global | 10 | |

(2 rows)

postgres=# \du+

List of roles

Role name | Attributes | Member of | Description

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

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |

postgres=# select * from pg_user;

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

postgres | 10 | t | t | t | t | ******** | |

(1 row)

postgres=# \dn+

List of schemas

Name | Owner | Access privileges | Description

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

public | postgres | postgres=UC/postgres+| standard public schema

| | =UC/postgres |

(1 row)

postgres=# create database mdb;

CREATE DATABASE

postgres=#

postgres=# \l+ mdb

List of databases

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

mdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8625 kB | pg_default |

(1 row)

postgres=# select oid,datname from pg_database;

oid | datname

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

14487 | postgres

16384 | mdb

1 | template1

14486 | template0

(4 rows)

postgres=# \c mdb

You are now connected to database "mdb" as user "postgres".

mdb=# \dS+

List of relations

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

(134 rows)

Database Administrator Tutorial

User defined database creation in postgresql

Database creation in PostgreSQL

Follow below steps to create database in PostgreSQL.

0 comments:

Post a Comment

Search

Labels