« PostgreSQL » : différence entre les versions
De Banane Atomic
Aller à la navigationAller à la recherche
(8 versions intermédiaires par le même utilisateur non affichées) | |||
Ligne 5 : | Ligne 5 : | ||
= Database = | = Database = | ||
<kode lang='pgsql'> | |||
-- list databases | |||
\l | |||
-- connect to a database | |||
\c myDatabaseName | |||
-- drop a database | |||
drop [if exists] myDatabaseName; | |||
</kode> | |||
<kode lang='bash'> | <kode lang='bash'> | ||
# create a database | # create a database | ||
Ligne 11 : | Ligne 22 : | ||
# connect to a database | # connect to a database | ||
psql -d myDatabaseName | psql -d myDatabaseName | ||
# drop a database | |||
dropdb myDatabaseName | |||
</kode> | </kode> | ||
Ligne 28 : | Ligne 37 : | ||
-- list all users and their permission levels | -- list all users and their permission levels | ||
\du | \du | ||
</kode> | |||
<kode lang='bash'> | |||
createuser --interactive | |||
dropuser <user> | |||
</kode> | </kode> | ||
Ligne 50 : | Ligne 64 : | ||
sudo -iu postgres | sudo -iu postgres | ||
# initialize the database cluster | # initialize the database cluster (postgres user) | ||
initdb -D /var/lib/postgres/data | initdb -D /var/lib/postgres/data | ||
# $LANG (en_US.UTF-8) is used to deduce the locale and the encoding | # $LANG (en_US.UTF-8) is used to deduce the locale and the encoding | ||
Ligne 60 : | Ligne 74 : | ||
sc-start postgresql.service | sc-start postgresql.service | ||
# create a user | # 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. | # 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 | createuser --interactive | ||
# create a database (postgres user) | |||
createdb dbtest | |||
# login (current user) | |||
psql -d dbtest | |||
</kode> | </kode> | ||
=== Access rights === | |||
<filebox fn='/var/lib/postgres/data/pg_hba.conf' lang='bash'> | |||
# 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 | |||
</filebox> |
Version du 23 mai 2023 à 22:15
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 |
Users and permissions
-- list all users and their permission levels \du |
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 |