SETUP PHP POSTGRESQL
Ammunition
- Ubuntu 22.04.3 LTS
Steps
-
Install PostgreSQL
sudo apt install postgresql postgresql-contrib -
Check Postgree Version
psql --version -
Check PostgreSQL status
sudo systemctl status postgresql -
If PostgreSQL is not running, start it with the command:
sudo systemctl start postgresql -
Enable PostgreSQL to start on boot:
sudo systemctl enable postgresql -
Login to postgresql as superuser
sudo -u postgres psql -
Create a database
CREATE DATABASE database_name; -
Create a user
CREATE USER user_name WITH PASSWORD 'password'; -
Grant privileges to the user
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name; -
Exit from postgresql
\q
Available Commands
Note : Assume you are logged in as superuser (with command: sudo -u postgres psql)
-
General Commands | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
\l| Show all databases | |\c database_name| Connect to a database | |\dt| Show all tables | |\d table_name| Show table structure | |\du| Show all users | |\dp| Show user privileges | |DROP DATABASE database_name;| Drop a database | |DROP USER user_name;| Drop a user | |GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;| Grant all privileges to a user | |REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;| Revoke all privileges from a user | |ALTER USER user_name WITH PASSWORD 'new_password';| Change user password | |\q| Exit from postgresql | -
Data Management Command | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
SELECT * FROM table_name;| Show all data in a table | |INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);| Insert data into a table | |UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;| Update data in a table | |DELETE FROM table_name WHERE condition;| Delete data from a table | |SELECT COUNT(*) FROM table_name;| Count total data in a table | |SELECT DISTINCT column_name FROM table_name;| Show distinct data in a column | -
Table Management Command | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...);| Create a table | |ALTER TABLE table_name ADD column_name datatype;| Add a column to a table | |ALTER TABLE table_name DROP COLUMN column_name;| Drop a column from a table | |ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;| Rename a column in a table | |ALTER TABLE table_name RENAME TO new_table_name;| Rename a table | |DROP TABLE table_name;| Drop a table | |TRUNCATE TABLE table_name;| Truncate a table | -
Index Management Command | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
CREATE INDEX index_name ON table_name (column_name);| Create an index | |DROP INDEX index_name;| Drop an index | -
Backup and Restore Command | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
pg_dump -U user_name database_name > file_name.sql| Backup a database | |psql -U user_name -d database_name -f file_name.sql| Restore a database | -
See Log and Status Command | Command | Description | | ----------------------------------------------------------------- | --------------------------------- | |
SELECT * FROM pg_stat_activity;| Show all active connections | |SELECT * FROM pg_locks;| Show all active locks | |SELECT pg_size_pretty(pg_database_size('database_name'));| Show database size |
Highlight :
-
If you want to delete a database, you cannot drop it while you're connected to it. PostgreSQL prevents deleting a database that is currently in use.
-
To successfully delete a database:
- Exit the current database session using
\qor connect to a different database (e.g.,postgres). - Connect to a different database (e.g.,
postgres) using\c postgres. - Drop the database with
DROP DATABASE database_name;. - Verify the deletion using
\lto list databases.
- Exit the current database session using
-
If there are active connections to the database you want to delete, you can terminate them manually before dropping the database:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'your_database_name';