Aller au contenu

Postgres#

Installation de Postgres (sur Alpine)#

Ici, nous installons la version13.12.

Info

Pour voir la liste des version disponible, utiliser: apk search postgres

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# Install Postgres
apk update
apk add --no-cache postgresql13=13.12-r0
apk add --no-cache postgresql13-contrib=13.12-r0

# Folder for PostgreSQL Socket
mkdir /run/postgresql
chown postgres:postgres /run/postgresql/

# Folder for PostgreSQL Data
su postgres -c "mkdir /var/lib/postgresql/data"
su postgres -c "chmod 0700 /var/lib/postgresql/data"

# Create New Database
su postgres -c "initdb -D /var/lib/postgresql/data"

Démarrer la Base de donnée#

Bash
1
2
3
4
su postgres -c "pg_ctl start -D /var/lib/postgresql/data"

# Test
su postgres -c "psql -c\"SELECT version();\""

Création Utilisteur & BD#

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
POSTGRES_DB=o7_db
POSTGRES_USER=mike
POSTGRES_PASSWORD=secret

# Create User
su postgres -c "psql -d template1 -c\"CREATE USER $POSTGRES_USER WITH PASSWORD '$POSTGRES_PASSWORD' CREATEDB;\""
su postgres -c "psql -d template1 -c\"CREATE DATABASE $POSTGRES_DB OWNER $POSTGRES_USER;\""

# Tests
psql -U $POSTGRES_USER -h postgres -d $POSTGRES_DB -c\"SELECT version();\""

Requete a partir de la ligne commande#

Installing PSQL on Amazon Linux#

Bash
1
2
3
4
5
# In sudo mode
amazon-linux-extras enable postgresql11
yum install postgresql -y
yum install jq -y
psql --version

Installing PSQL on Debian#

Bash
1
2
3
4
# In sudo mode
apt install postgresql-client
apt install jq
psql --version

Init Varibles#

Bash
1
2
3
4
export PGHOST=super.cluster-xxxxxxxx.ca-central-1.rds.amazonaws.com
export PGDATABASE=o7_db
export PGUSER=mike
export PGPASSWORD=secret

Init Varibles with AWS Secret#

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export SECRET_KEY=Name
export SECRET_VALUE=my-secret

SECRET_ARN=$(aws secretsmanager list-secrets --query 'SecretList[]' | jq ".[] | select(.Tags[]? | (.Key==\"$SECRET_KEY\" and .Value==\"$SECRET_VALUE\"))" | jq -r '.ARN')
SECRET=$(aws secretsmanager get-secret-value --secret-id  $SECRET_ARN --query SecretString --output text

export PGHOST=$(echo $SECRET | jq -r '.host')
export PGDATABASE=$(echo $SECRET | jq -r '.dbname')
export PGUSER=$(echo $SECRET | jq -r '.username')
export PGPASSWORD=$(echo $SECRET | jq -r '.password')

Query example#

Bash
1
2
3
4
5
6
QUERY="SELECT version();"
psql -c "$QUERY"

# get all clients in database
QUERY="SELECT * FROM clients;"
psql -c "$QUERY"

Site de Référence#

https://luppeng.wordpress.com/2020/02/28/install-and-start-postgresql-on-alpine-linux/