Postgresql

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/17

18 Terms

1
New cards

what is pg_ctl ?

pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

2
New cards

what does initdb ?

The initdb command initializes a PostgreSQL data directory, which is the foundational step to set up a PostgreSQL server.
It:

  1. Creates System Tables

  1. Creates a Default Superuser (system user if not set explicitly)

  2. Sets up Configuration Files, postgresql.conf (main configuration file) and pg_hba.conf (authentication settings)

  3. Creates a Default Database (postgres database is created for administrative purposes.)



3
New cards

What does createdb do ?

The createdb command creates a new user-defined database within an already-initialized PostgreSQL instance.

4
New cards

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:

  1. Data Directory

  2. Shared Resources

  3. Single PostgreSQL server process

  4. Multiple databases (including default postgres)

  5. Roles and permissions.


5
New cards

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.

6
New cards

How can you create a user ?

CREATE ROLE nom_utilisateur WITH LOGIN PASSWORD 'mot_de_passe';

7
New cards

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;


8
New cards

How can you list all the roles (users) in a cluster ?

connect to the cluster then execute \du

9
New cards

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.


10
New cards

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';


11
New cards

How to list all databases in the PostgreSQL database server ?

Execute the command: \l

12
New cards

How to list all tables in a current database ?

Execute the command: \dt

13
New cards

How to get detailed information on a table ?

Execute the command: \d+ table_name

14
New cards

How to create a new database ?

Execute this SQL query:

CREATE DATABASE [IF NOT EXISTS] db_name;


15
New cards

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,
   ...
);


16
New cards

How to add a new column to a table ?

Execute the SQL query:

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;


17
New cards

How to delete a column in a Table ?

Execute the SQL query:

ALTER TABLE table_name
DROP COLUMN column_name;


18
New cards

How to show the current databse ?

Execute the SQL query:

SELECT current_database();