1/17
what is pg_ctl ?
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.
what does initdb ?
The initdb command initializes a PostgreSQL data directory, which is the foundational step to set up a PostgreSQL server.
It:
Creates System Tables
Creates a Default Superuser (system user if not set explicitly)
Sets up Configuration Files, postgresql.conf (main configuration file) and pg_hba.conf (authentication settings)
Creates a Default Database (postgres database is created for administrative purposes.)
What does createdb do ?
The createdb command creates a new user-defined database within an already-initialized PostgreSQL instance.
What is a PostgreSQL Database Cluster ?
A database cluster in PostgreSQL refers to a single instance of the PostgreSQL server that manages multiple databases. It is essentially the entire environment that includes:
Data Directory
Shared Resources
Single PostgreSQL server process
Multiple databases (including default postgres)
Roles and permissions.
What is a User-Defined Database ?
A user-defined database is a single database within a PostgreSQL cluster, created explicitly by a user for storing and managing data. Each database is independent in terms of its schema, tables, and data, but it operates within the context of the cluster.
How can you create a user ?
CREATE ROLE nom_utilisateur WITH LOGIN PASSWORD 'mot_de_passe';
How can you grant privileges to a user ?
To create databases:
ALTER ROLE user_name CREATEDB;
To create other roles or users:
ALTER ROLE user_name CREATEROLE;
To grant superuser privileges:
ALTER ROLE mon_utilisateur SUPERUSER;
How can you list all the roles (users) in a cluster ?
connect to the cluster then execute \du
What happen when you connect to a database within a PostgreSQL cluster ?
When you connect to a specific database, you are connected to the cluster through that database.
Operations can be database-specific (e.g., creating a table) or cluster-wide (e.g., creating a role or database).
The extent of what you can do depends on your privileges and the type of operation.
How can you change a role (user) password ?
By using the command \password if you change your own password
Or
execute:
ALTER ROLE username WITH PASSWORD 'new_password';
How to list all databases in the PostgreSQL database server ?
Execute the command: \l
How to list all tables in a current database ?
Execute the command: \dt
How to get detailed information on a table ?
Execute the command: \d+ table_name
How to create a new database ?
Execute this SQL query:
CREATE DATABASE [IF NOT EXISTS] db_name;How to create a new table ?
Execute the sql query:
CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(
pk SERIAL PRIMARY KEY,
c1 type(size) NOT NULL,
c2 type(size) NULL,
...
);How to add a new column to a table ?
Execute the SQL query:
ALTER TABLE table_name ADD COLUMN new_column_name TYPE;How to delete a column in a Table ?
Execute the SQL query:
ALTER TABLE table_name
DROP COLUMN column_name;How to show the current databse ?
Execute the SQL query:
SELECT current_database();