66471934af
Knowledge base of ~80+ markdown files across 14 domains (00-13), Logseq graph, hardware design files (KiCAD), infrastructure configs, and talas-wiki static site. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
166 lines
3.4 KiB
Text
166 lines
3.4 KiB
Text
sudo -i
|
|
|
|
# update package index
|
|
apt update
|
|
|
|
#install tools
|
|
apt install lsb-release curl gpg -y
|
|
|
|
# add postgresql 17 repository
|
|
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
|
|
|
|
# import repo signin key
|
|
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
|
|
|
|
# update package index
|
|
apt update
|
|
|
|
# install postgresql 17
|
|
apt install postgresql-17 -y
|
|
|
|
# check status
|
|
systemctl status postgresql
|
|
# start postgresql service
|
|
systemctl start postgresql
|
|
# enable postgresql service
|
|
systemctl enable postgresql
|
|
|
|
|
|
# confirm checking status
|
|
systemctl status postgresql
|
|
|
|
|
|
# INITIALIZE POSTGRESQL
|
|
|
|
# switch to postgres user
|
|
su - postgres
|
|
|
|
# access postgres SQL shell
|
|
psql
|
|
|
|
# check version of postgres
|
|
SELECT VERSION();
|
|
|
|
# list databases
|
|
\l
|
|
# or list databases with more infos
|
|
\l+
|
|
|
|
# switch to specific database
|
|
\c db_name
|
|
|
|
# check tables
|
|
\dt
|
|
|
|
|
|
|
|
|
|
# INITIALIZE POSTGRESQL INTO THE INCUS CONTAINER TO ACCEPT LOCAL HOST REQUESTS #
|
|
|
|
# edit postgresql.conf file
|
|
vim /etc/postgresql/17/main/postgresql.conf
|
|
|
|
# uncomment and edit as follow
|
|
listen_addresses = '*'
|
|
|
|
# edit pg_hba.conf
|
|
vim /etc/postgresql/17/main/pg_hba.conf
|
|
|
|
# add following new line (see beginning of the file for explanations on options). note that 0.0.0.0/0 allow any adresse ip as requester
|
|
host all all 0.0.0.0/0 scram-sha-256
|
|
|
|
# restart postgresql
|
|
systemctl restart postgresql
|
|
|
|
|
|
# try to ping the container ip adrress from yout local host and then the opposite
|
|
ping 10.78.13.30
|
|
|
|
# if the ping is working, connect to postgresql using a client as psql
|
|
psql -h 10.184.116.91 -U testuser -d testdb
|
|
# check tables
|
|
\dt
|
|
# show content
|
|
SELECT * FROM employees;
|
|
|
|
|
|
|
|
|
|
# simple test DB in SQL
|
|
CREATE DATABASE testdb;
|
|
|
|
CREATE TABLE employees (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100),
|
|
position VARCHAR(50),
|
|
salary NUMERIC(10, 2)
|
|
);
|
|
|
|
|
|
|
|
|
|
# create postgresql user
|
|
CREATE USER okinrev WITH PASSWORD 'supermotdepassse';
|
|
|
|
# give permission on that database for the newly created user
|
|
GRANT ALL PRIVILEGES ON DATABASE testdb TO okinrev;
|
|
|
|
# grant access on specific public table
|
|
GRANT SELECT ON public.employees TO testuser;
|
|
host replication replicator 10.184.116.221/0 scram-sha-256
|
|
# remove permission from user
|
|
REVOKE ALL PRIVILEGES ON DATABASE testdb FROM okinrev;
|
|
|
|
# remove user
|
|
DROP USER okinrev;
|
|
|
|
sudo -i -u postgres
|
|
ssh-keygen -t rsa -b 2048 -f ~/.ssh/id_rsa -N ""
|
|
copie ssh key to pitr server
|
|
|
|
wal_level = replica
|
|
|
|
archive_mode = on
|
|
|
|
archive_command = 'rsync -a %p root@10.184.116.233:/srv/wal/%f'
|
|
restore_command = 'rsync -a root@10.184.116.233:/srv/wal/%f %p'
|
|
|
|
recovery_target_time = '2024-12-30 15:05:00'
|
|
|
|
|
|
|
|
|
|
touch /var/lib/postgresql/17/main/recovery.signal
|
|
|
|
|
|
|
|
rsync -a root@10.184.116.233:/srv/wal/%f
|
|
|
|
|
|
|
|
INSERT INTO employees (name, position, salary)
|
|
VALUES
|
|
('Intru', 'Malicieux', 99999.00);
|
|
|
|
14:10:50 execution
|
|
|
|
|
|
host replication replicator 10.184.116.221/0 scram-sha-256
|
|
|
|
|
|
|
|
DO $$
|
|
DECLARE
|
|
i INT;
|
|
BEGIN
|
|
FOR i IN 1..2000000 LOOP
|
|
INSERT INTO employees (name, position, salary)
|
|
VALUES (
|
|
'Ouioui ' || i, -- Chaîne avec l'indice pour éviter les doublons
|
|
'Mechant ' || i, -- Position avec l'indice
|
|
77777.00 -- Salaire constant
|
|
);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
execution 15:59:45
|