Postgres Tutorial
PostgreSQL Basic Commands
1. Log in as the PostgreSQL User
sudo -i -u postgres
2. Start the PostgreSQL Shell
psql
Database Operations
3. Create a Database
CREATE DATABASE db1;
4. List All Databases
\l
5. Connect to a Database
\c db1
Table Operations
6. Create a Table
CREATE TABLE student (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
department VARCHAR(50)
);
7. Insert Records
INSERT INTO student (name, age, department)
VALUES
('Sundar', 25, 'Computer Science'),
('Priya', 22, 'Electronics');
8. View Inserted Data
SELECT * FROM student;
9. List Tables in the Current Database
\dt
10. Describe the Table Structure
\d student
Backup and Restore
11. Exit the PostgreSQL Shell
\q
12. Take a Backup of db1
From the Linux terminal (logged in as the postgres user):
pg_dump -d db1 -f /tmp/db1_backup.sql
Verify the backup file:
ls -lh /tmp/db1_backup.sql
13. Create a New Database for Restore
Start psql:
psql
Create the database:
CREATE DATABASE db2;
Exit:
\q
14. Restore the Backup into db2
From the Linux terminal:
psql -d db2 -f /tmp/db1_backup.sql
15. Verify the Restore
Start psql:
psql
Connect to the restored database:
\c db2
List the tables:
\dt
View the data:
SELECT * FROM student;