edib

sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y
 
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS hstore;'"
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";'"
sudo systemctl stop postgresql
sudo su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.6/bin \
-d /var/lib/postgresql/9.3/main/ -D /var/lib/postgresql/9.6/main/ \
-O "-c config_file=/etc/postgresql/9.6/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" --link'
 
sudo apt-get remove postgresql-9.3 -y

edib

# See https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian for more information and warning!

# Check first
/usr/lib/postgresql/11/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.6/bin \
   -B /usr/lib/postgresql/11/bin \
   -d /var/lib/postgresql/9.6/main \
   -D /var/lib/postgresql/11/main \
   -o ' -c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/11/main/postgresql.conf' \

edib

To use the most modern version of Postgres software we need to add postgresql repository. Edit /etc/apt/sources.list or create /etc/apt/sources.list.d/pgdg.list and add there a line: deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main Then import the repository signing key, and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install a new version of PostgreSQL server.

Once the Debian upgrade finished, I used dpkg-query -l postgresql* to check which versions of postgres I have installed.

edib

-- Order by one indexed column (FAST)
newsdesk_production=# explain analyze select * from pressreleases order by published_at DESC limit 100;
                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..249.91 rows=100 width=1207) (actual time=26.070..716.453 rows=100 loops=1)
   ->  Index Scan Backward using pressreleases_published_at_index on pressreleases  (cost=0.00..964766.62 rows=386042 width=1207) (actual time=26.067..716.343 rows=100 loops=1)
 Total runtime: 716.709 ms
(3 rows)

- Order by two separately indexed columns (SLOW)

edib

-- show running queries (pre 9.2)
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(query_start, clock_timestamp()), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 

edib

Edit /var/lib/postgres/data/postgresql.conf:

# change IP on subscriber
listen_addresses = '*'
wal_level = logical
shared_preload_libraries = 'pglogical'
max_worker_processes = 16
max_wal_senders = 16
max_replication_slots = 16
track_commit_timestamp = on

edib

## This will be fixed by

find /var/www -type d -exec chmod 755 {} \;
find /var/www -type f -exec chmod 644 {} \;

edib

# Sample haproxy postgresql master check
#
# haproxy listen: 5431
# pg, instance #1 listen: 5432 (master node)
# pg, instance #2 listen: 5433 (replica node)
# external failover, promoting replica to master in case of failure
# passwordless auth for user web
# template1 database is accessible by user web
#
# haproxy will pass connection to postgresql master node:

edib

--- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md
--- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192

-- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB.

------------
-- Basics --
------------

-- Get indexes of tables

edib

Bu yazıyı okumadan önce, ssh ayarlarınızı yaptığınıza emin olun
Bu yazı PostgreSQL 11 ve PgBackRest 2.15 için yazılmıştır

Kurulum

Master ayarları

Öncelikle aşağıdaki komut ile pgbackrest kuralım:

sudo apt-get install pgbackrest -y