PostgreSQL basic

Ravi Suthan — Thu, 21 Nov 2024 05:42:21 +0000

\c -- database connected
\l -- listout of databases
\l+-- listout databse with database size
\dt--listout the tables from databases
\q--exit from psql
sudo systemctl start postgresql -- start the postgressql server
sudo systemctl restart postgresql -- restart the postgresql server
sudo systemctl stop postgresql -- stop the postgressql server
journalctl -u postgresql -- postgressql logs
Primary (Master) Server: This server will send its WAL (Write-Ahead Log) data to the standby server. enble the WAL
Standby (Replica/Slave) Server: This server will receive and apply the WAL data from the primary server, keeping itself in sync.
SELECT datname FROM pg_database;
pg_dump command for logical backups
pg_basebackup for physical backups
default portnumber--5432
sudo -i -u postgres postgres connect commond linux machine
sudo su switchuser
sudo su - username
cd change dirctory
ls listout dirctory
ls -r show the reverse process files
ls -R show the folder inside file
ls -a show the hidden files
ls -l show the file/directory size,owner,created date
ls -l filename* filter condition file
ls -f show the folder end /
cat -- view the inside file data cat -n
ctrl o enter ctrl x -- save the file in nano format
esc button : x --- save the file normal format.
tail -- view the data last row tail -n bottom
head -- view the data bregin row tail -- n
ps -- monitoring of process
ps -ef -- monitoring process
top -- process of cpu monitoring
vi -- edit the file
mv oldname.txt newname.txt --rename moving files cmd mv doc/fall ls -R
rm -i removefilename y -- remove delete file.
rm -ir deletedirectoryname y -- remove delete dirctory.
rm -iF force deleting
wild card method ? ls -l f?ll
find --- find . -name/marvel find the folder and dirctory.
cp test1 test2 -- copy the file test1 to test2
cp test1 /home/appuser/anotherdirctory
ln -s filename sl_filename --create symbolicfile name
ln filename hl_filename -- create hardlink file name
RAM: free -h or cat /proc/meminfo
CPU: lscpu or cat /proc/cpuinfo
Disk: df -h / du -h
kill -- kill process id
free -h -- memory
ls -l list and file type access
df -th drive sapce and type
du -sh disk useage size of files
grip three filename cmd -- find
df -h size
du -sh/etc
gzip filename -- compress the file
gzip -c filename > file.gz -- rename compress file
gzip -d filename -- uncompress the filename
unzip filename -d --- unzip
lsb_release -a version
tar -- compress and uncompress file.
SSH -- review
service --
traceroute --
ipconfig--
wget --
ufw -- Fire wall related cmd
cd ..
cd /home/appuser/
mkdir make dirctory
rmdir remove dirctory
cp filename foldername copy
touch -- file create touch file1 file2 file3
permission based cmd
chmod u -w --- changemode denined u write permission
chmod g +w -- changemode grnated group write permission.
u user/owner g -- group a-all
chown username filename -- change ownername
rwx
passwd
useradd,usermod
peckage mangement system.
debian -- ubuntu/linux mint apt ,packman
redhot -- centos/fedora yum list installed / yum,rpm zip install
sudo
ctrl c down
cltr l clear
hostnamectl -- os verion
psql -U postgres -d mydatabase -- database connection
psql -h 10.20.56.134 -p 5432 -U admin -d postgres -- postgres connet command
whoami -- current user name
pwd- current dirctory
uname -os name
uname -r -- version
date - date
hostname -- show the hostname
hostname -i -- hostname ip
10.20.56.134
10.20.96.209

appuser

P@ssw0rd@12

database creation ;

sudo -i -u postgres # Switch to the postgres user
psql # Access the PostgreSQL shell
CREATE DATABASE mydatabase; # Create a new database
\l # List all databases to verify creation
\q # Exit the psql shell
psql -d mydatabase # Connect to your new database (optional)

backup command ;

pg_dump -U postgres -d mydatabase -f /path/to/backup/file.sql

create login :
CREATE ROLE username WITH LOGIN PASSWORD 'password';
ALTER ROLE username CREATEDB;
ALTER ROLE username SUPERUSER;

import tool in postgrssql :

1.pgLoader-- tool (linux)ubentu / centos

-Automated schema conversion.
-Efficient data transfer.
-Support for complex data types.
-Minimal downtime migration.

2.SQL Server Integration Services (SSIS) (windows) SSDT (GUI)

3.DBConvert
DBConvert is a commercial tool with a user-friendly interface for database conversion.

Features:

Support for both schema and data conversion.
Customizable migration settings.
Data validation and consistency checks.

always encription / Case-insensitive encryption

Install the Foreign Data Wrapper -- Linked server concept

-- Install the mysql_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Define the foreign server
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', port '5432', dbname 'remote_db');

-- Create a user mapping
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'remote_user', password 'remote_password');

-- Create a foreign table
CREATE FOREIGN TABLE local_mysql_table (
id integer,
name text,
value numeric
)
SERVER mysql_server
OPTIONS (dbname 'mysql_db', table_name 'mysql_table');

-- Query the foreign table
SELECT * FROM local_mysql_table;

Verion:

ersion Type Example Purpose Usage
Major Version 13, 14, 15 Introduces new features and improvements Production (after thorough testing)
Minor Version 13.1, 13.2 Provides bug fixes and security patches Production
Release Candidate 15rc1, 15rc2 Pre-release versions for final testing Testing
Beta Version 15beta1, 15beta2 Early testing of new features Testing
Development Snapshots - Daily or periodic builds for development and early testing Development only****

DEV Community: Ravi Suthan

PostgreSQL basic