Environment Configuration#
Installing Client and Server Tools#
For details, refer to the steps indicated in the link: https://www.postgresql.org/download/
(Only) Installing Client Connection Tools#
# Debian operating system
sudo apt update
sudo apt install postgresql-client
# MacOS operating system
brew install libpq
Service Management#
To manage services on Linux operating systems, use systemctl. The service name is postgresql, and you can use commands such as:
# Start service
systemctl status postgresql
# Restart service
systemctl restart postgresql
# Reload configuration file
systemctl reload postgresql
# Stop service
systemctl stop postgresql
User Management#
-- Create user
CREATE USER <username> WITH PASSWORD '<password>';
-- Grant privileges (single database)
GRANT ALL PRIVILEGES ON DATABASE <database> TO <username>;
-- Grant privileges (single table)
ALTER TABLE <table_name> OWNER TO <username>;
-- Change password
ALTER USER <username> WITH PASSWORD <new_password>;
PostgreSQL Built-in Statements#
-- List all databases on the server
\l
\list
-- Use a specific database
\c <db_name>
\connect <db_name>
-- List all tables in the database
\dt
-- List all tables and views in the database
\d
\dtvmsE
-- Get the structure of a specific table
\d <table_name>
-- Import SQL file
\i <filename>
\include <filename>
DDL (Data Definition Language)#
-- Create database
CREATE DATABASE mydb;
-- Drop database
DROP DATABASE mydb;
psql (PostgreSQL interactive terminal)#
Execute SQL statements in command line#
psql -h <host> -d <db_name> -U <username> -d <database> -c "<sql>"
Execute SQL statements without password in command line#
Method 1: Environment variables#
export PGPASSWORD='<password>'
After configuring, you can execute commands normally, for example:
Method 2: .pgpass configuration file#
Create a .pgpass file in the user's home directory (e.g., ~/) with the following content (replace the content in angle brackets with actual information):
<host>:<port>:<db_name>:<username>:<password>
Next, set the permissions for the .pgpass file so that only the user can read and write to it:
chmod 600 "$HOME/.pgpass"
Then try executing commands without password:
psql -h <host> -d <db_name> -U <username> -c "<sql>"
Additionally, you can specify a custom path for .pgpass by configuring the PGPASSFILE environment variable:
export PGPASSFILE="/path/to/.pgpass"
Method 3: pg_service.conf configuration file#
Create a .pg_service.conf configuration file in the user's home directory and fill in the following content (replace the content in angle brackets with actual information):
[myservice]
host=<host>
port=<port>
dbname=<db_name>
user=<username>
password=<password>
Then configure the PGSERVICE environment variable to the specified name in the configuration file:
export PGSERVICE="myservice"
Finally, try executing commands without password:
psql -c "<sql>"
Note that using .pg_service.conf allows you to store multiple service connection configurations. To switch to a different configuration, simply modify the PGSERVICE environment variable to the specific name.
Additionally, you can specify a custom path for .pg_service.conf by configuring the PGSERVICEFILE environment variable:
export PGPASSFILE="/path/to/.pg_service.conf"