database

Postgresql

Changement de version

Avec archlinux tout va très vite. Et lorsqu´ils décident de changer de version majeure de postgres, après le reboot c´est le drame.

Comment gérer la crise?

Simplement pg_upgrade

# creer un noveau cluster postgres
initdb -D /mnt/cryptdisk/Postgres/data --locale=en_US.UTF-8

## installer les paquets postgresql-old-upgrade
pacman.install postgresql-old-update

## realiser la migration
pg_upgrade -b /opt/pgsql-11/bin -B /usr/bin -d /opt/postgres/data -D /mnt/cryptdisk/Postgres/data

## lancer le vacuum
./analyze_new_cluster.sh

## supprimer les anciennes données
./delete_old_cluster.sh

Il faut aussi penser à modifier les scripts systemd:

...
# en particulier la variable PGROOT
Environment=PGROOT=/mnt/cryptdisk/Postgres
...

Désormais, la dernière version de postgres est utilisée.

Calculer les statistiques

VACUUM ANALYZE <table>;

Récupérer les éléments de statistiques

SELECT schemaname, relname, last_analyze 
FROM pg_stat_all_tables 
WHERE relname = 'city';

Configurer le cluster

This website is helpful

Install postgis

source :

yum install postgis30_12.x86_64
yum install postgis30_12-utils.x86_64
-- Enable PostGIS (as of 3.0 contains just geometry/geography)
CREATE EXTENSION postgis;
-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Passer des variables à un script via psql

psql -v var1=foo -v var2=bar -f file.sql

On peut à la fois passer des variables simples et des variables inclues dans des chaines de caractères.

select *
from :var1
where :'var2'

Réaliser un dump

ce script réalise un dump binaire, avec 4 threads:

#!/bin/bash
set -e
TMZ=$(date +"%Y%m%d-%H%M%S")

PG_VERSION=12.1
PG_HOST=<the host>
DATABASE=<the database>
PG_USER=<the user>
THREAD=4
PG_FOLDER=/usr/pgsql-$PG_VERSION/bin
DUMP_FOLDER=/dump/$DATABASE/

$PG_FOLDER/pg_dump -h $PG_HOST -p 5432 -U $PG_USER -w -Fd $DATABASE -n <schema1> -n <schema2> -j$THREAD -f $DUMP_FOLDER/
echo "create database $DATABASE" > $DUMP_FOLDER/create_database.sql
echo "\c $DATABASE" >> $DUMP_FOLDER/create_database.sql
$PG_FOLDER/pg_dumpall -h $PG_HOST -p 5432 -U $PG_USER -w --globals-only >> $DUMP_FOLDER/create_database.sql

Charger un dump

#!/bin/bash
set -e
TMZ=$(date +"%Y%m%d-%H%M%S")

PG_VERSION=12
PG_HOST=<the host>
DATABASE=<the db>
USER=<the user>
THREAD=4
PG_FOLDER=/usr/pgsql-$PG_VERSION/bin
DUMP_FOLDER=/tmp/$DATABASE/

cat  $DUMP_FOLDER/create_database.sql > $PG_FOLDER/psql -U $USER -W -h  $PG_HOST -d postgres
$PG_FOLDER/pg_restore -Fd $DUMP_FOLDER -n <schema1> -n <schema2> -j$THREAD  -U $USER  -h  $PG_HOST -d $DATABASE

Table corrompue

psycopg2.errors.DataCorrupted: invalid page in block 2 of relation base/1687445/1688449

  1. read the postgresql logs to know the name of the relation
  2. SET zero_damaged_pages = on;
  3. VACUUM FULL damaged_table;
  4. REINDEX TABLE damaged_table;

from

psqlrc

\timing on
\set pager 0
\x auto

\set HISTCONTROL ignorespace
\set HISTSIZE -1
This page was last modified: