PostgreSQL
De Banane Atomic
Aller à la navigationAller à la recherche
Links
Database
|
-- list databases \l -- connect to a database \c myDatabaseName -- drop a database drop [if exists] myDatabaseName; |
|
# create a database createdb myDatabaseName # connect to a database psql -d myDatabaseName # drop a database dropdb myDatabaseName |
Table
|
|
-- show summary information about all tables in the current database
\dt
CREATE TABLE [table] (
[column] integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
[column] varchar(40) NOT NULL CHECK ([column] <> '')
);
|
Users and permissions
|
|
-- list all users and their permission levels \du CREATE USER [user] WITH PASSWORD 'xxxxxx'; |
|
|
createuser --interactive dropuser <user> |
Service
|
|
sc-start postgresql.service |
Installation
|
|
# will install postgresql-libs, postgresql and create a system user called postgres sudo pacman -S postgresql |
Configuration
|
|
# create the cluster directory if needed sudo mkdir /var/lib/postgres sudo chown postgres:postgres /var/lib/postgres # switch to the PostgreSQL user sudo -iu postgres # initialize the database cluster (postgres user) initdb -D /var/lib/postgres/data # $LANG (en_US.UTF-8) is used to deduce the locale and the encoding # or can be defined manually: initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums # --data-checksums enable data checksumming # start the service sc-start postgresql.service # login (postgres user) psql # create a user (postgres user) # If the new user has the same name as your Linux user, it allows you to access the PostgreSQL database shell without having to specify a user to login. createuser --interactive # create a database (postgres user) createdb dbtest # login (current user) psql -d dbtest |
Access rights
| /var/lib/postgres/data/pg_hba.conf |
# all local users can access to any database including superuser database local all all trust # only local postgres user can login local all postgres peer |
Installation on Windows
|
winget install PostgreSQL.PostgreSQL |
 |
default user and password: postgres |
|
|
# connection to the database C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db] |
