31.10.2024

Administration

How to Manage Databases in PostgreSQL

Manage PostgreSQL Databases on Your AlexHost VPS

Why run PostgreSQL on AlexHost? PostgreSQL (Postgres) is a robust, open-source relational database perfect for scalable apps, and AlexHost’s VPS with NVMe storage, root access, and DDoS protection ensures top performance and security. This guide covers essential PostgreSQL management commands—creating databases, tables, users, backups, and more—optimized for your AlexHost Linux VPS.

1. Accessing the PostgreSQL Command Line

To manage PostgreSQL databases, first access the PostgreSQL command-line interface (CLI). By default, PostgreSQL uses the postgres user account.

Step 1: Switch to the PostgreSQL User

sudo -i -u postgres

Step 2: Access the PostgreSQL CLI

psql

Once in the CLI, you’ll see the postgres=# prompt, indicating you’re in the PostgreSQL environment.

2. Creating a New Database

To create a new database, use the CREATE DATABASE command. The syntax is as follows:

CREATE DATABASE database_name;

Example:

CREATE DATABASE my_database;

This creates a database named my_database. To verify, use:

\l

3. Creating and Managing Users

Database users need permission to access and modify databases. Here’s how to create and manage users in PostgreSQL.

Create a New User

CREATE USER username WITH PASSWORD 'password';

Example:

CREATE USER dbuser WITH PASSWORD 'securepassword';

Grant Database Access

To allow a user to access and manage a database, use the GRANT command:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Example:

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

This grants dbuser full access to my_database.

4. Connecting to a Database

To connect to a specific database, use the \c command followed by the database name.

\c my_database

The prompt will change to indicate the active database, allowing you to manage tables, insert data, and perform queries.

5. Creating and Managing Tables

Tables store data in a structured format. Here’s how to create, modify, and delete tables in PostgreSQL.

Create a Table

Use the CREATE TABLE command to define a new table and its columns:

CREATE TABLE table_name ( column1 data_type PRIMARY KEY, column2 data_type, column3 data_type );

Example:

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary NUMERIC );

Insert Data into a Table

Insert records using the INSERT INTO statement:

INSERT INTO employees (name, department, salary) VALUES ('John Doe', 'HR', 50000);

Query Data

Retrieve data from a table using SELECT:

SELECT * FROM employees;

Update Data

To update records in a table:

UPDATE employees SET salary = 55000 WHERE name = 'John Doe';

Delete Data

To delete specific rows:

DELETE FROM employees WHERE name = 'John Doe';

6. Managing Database Access and Security

For security, grant only necessary privileges to users:

Revoke Access:
REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;
Restrict Table Permissions:
GRANT SELECT ON TABLE employees TO dbuser;

This allows dbuser to only view the employees table data without making changes.

7. Backing Up and Restoring Databases

PostgreSQL offers commands for database backup and restoration.

Backup a Database

Use the pg_dump command to back up a database to a file:

pg_dump my_database > my_database_backup.sql

Restore a Database

Use the psql command to restore a database from a backup file:

psql my_database < my_database_backup.sql

8. Dropping Databases and Tables

When a database or table is no longer needed, use the DROP command to delete it.

Drop a Table

DROP TABLE table_name;

Example:

DROP TABLE employees;

Drop a Database

Exit the database first (if connected), then drop it:

DROP DATABASE database_name;

Example:

DROP DATABASE my_database;

9. Exiting PostgreSQL

To exit the PostgreSQL command-line interface, use:

\q

Conclusion: PostgreSQL Mastery on AlexHost

Managing PostgreSQL on your AlexHost VPS is straightforward—create databases, tables, and users, secure access, and automate backups with pg_dump. AlexHost’s NVMe storage and root access ensure fast, reliable database operations for your apps. Use CLI commands like CREATE, GRANT, and \l to stay in control, and lean on AlexHost’s security and support for peace of mind. Build robust databases and keep your data humming!