If you don't use PostgreSQL frequently, you may often forget even the basic commands. To help with that, I have compiled the main commands commonly used for PostgreSQL setup and management. I hope this guide serves as a useful reference when you need to use PostgreSQL again.
1. Connecting to PostgreSQL
To use PostgreSQL commands, you must first connect to the database. Here’s how to connect using the default admin account, postgres:
sudo -u postgres psqlOnce you enter this command, you will access the PostgreSQL shell and can execute various SQL commands.
2. Creating a Database
To create a new database, use the following command:
CREATE DATABASE database_name;Here, replace database_name with the desired name for the new database.
3. Creating a User
In PostgreSQL, you can grant individual permissions to each user. To create a new user and set a password, use the following command:
CREATE USER user_name WITH PASSWORD 'password';Replace user_name and 'password' with your desired username and password.
4. Granting User Permissions on a Database
To grant a specific user access rights to a database, use the following command:
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;If you wish to grant only specific permissions, you can specify commands like SELECT, INSERT, or UPDATE instead of ALL PRIVILEGES.
5. Listing Databases
To see all databases on the server, use the following command:
\lThis command is used within the PostgreSQL shell to display the list of databases on the current server.
6. Listing Users
To list all users created in PostgreSQL, use the following command:
\du7. Connecting to a Specific Database
When you want to switch to another database, connect using the following command:
\c database_nameReplace database_name with the name of the database you wish to connect to.
8. Viewing the List of Tables
To view all tables in the currently connected database, use the following command:
\dtThis command allows you to quickly check the list of tables in the current database.
9. Deleting a Database
To delete an unnecessary database, use the following command:
DROP DATABASE database_name;Warning: This command will permanently delete the database and all its data, so use it with caution.
10. Deleting a User
To delete a user that is no longer needed, input the following command:
DROP USER user_name;11. Granting Permissions on a Specific Table
To grant permissions to a user on a specific table, you can use the following command:
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO user_name;Here, table_name is the name of the table to which you are granting permissions.
12. Granting Superuser Privileges
If you want to grant a specific user superuser privileges, use the following command:
ALTER USER user_name WITH SUPERUSER;This command sets the user as a superuser with all permissions in the database.
13. Changing a User's Password
To change the password of an existing user, use the following command:
ALTER USER user_name WITH PASSWORD 'new_password';14. Controlling the PostgreSQL Service
The PostgreSQL service can be started, stopped, and restarted on the server. The following commands are useful for managing the PostgreSQL service.
# Start the service
sudo systemctl start postgresql
# Stop the service
sudo systemctl stop postgresql
# Restart the service
sudo systemctl restart postgresql
15. Backing Up and Restoring a Database
To back up or restore a database, use the pg_dump and pg_restore commands.
- Backup:
pg_dump -U user_name -W -F t database_name > backup_file.tar - Restore:
pg_restore -U user_name -W -d database_name backup_file.tar
You can easily restore the database using the backup file, preparing you for unexpected situations.
If you remember these commands well, managing and setting up PostgreSQL will become much easier. Refer to this list whenever necessary to get your tasks done quickly.
Add a New Comment