How a MySQL DBA see Postgresql (and why their company should worry - - PowerPoint PPT Presentation

How a MySQL DBA see Postgresql (and why their company should worry about)

Marco Tusa Percona

2

About me

Marco “The Grinch”

• Former UN, MySQL AB, Pyt

hian, Percona

• 2 kids, 1 wife
• History of Religions;

Ski; Snowboard; Scuba Divin g;

3

Learn how to ride the Dolphin

4

How to eat an elephant?

My Motto

Use the Right Tool for the Job

6

Summary

• Installing
• Basic configuration
• Security definition
• Create a database and tables
• Backups
• Replication
• The magic behind indexes

7

Installation

• Create a Postgres user and assign a home directory with the required

privileges

• Packages RPM/DEB
• Source
• Installation packages
• https://www.postgresql.org/download/

8

Basic configuration

• postgresql.conf
• postgresql.auto.conf
• pg_hba.conf
• pg_ident.conf
• postmaster.opts
• postmaster.pid

[root@pg1h1p81 data]# cat postmaster.pid 30452 <------------- PID /var/lib/pgsql/10/data <------------- data dir 1539681530 <--------------Start timestamp 5432 <-------------- tcp/ip Port /tmp <-------------- temp dir * <--------------IP address bound 5432001 196608 <-------------- second is shmid Ready <-------------- Server status

9

Basic configuration 2

• ALTER SYSTEM SET work_mem TO '16MB’;
• Check postgresql.auto.conf
• pg_hba.conf

# IPv4 local connections: # TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 md5 host all all 192.168.1.1/24 md5 host replication all 192.168.1.1/24 md5

10

Basic configuration 3

• shared_buffer 20% RAM
• wal_buffers is 16MB, but if you have a lot of concurrent connections then a

higher value can give better performance.

• effective_cache_size 4GB Sets the planner's assumption about the size of the

disk cache.

• effective_io_concurrency 1 default I change it to 6 (based on my disk layout)

| Number of simultaneous requests that can be handled efficiently by the disk subsystem.

• work_mem (def 4Mb ) This configuration is used for complex sorting. A mix

between sort_buffers and other per session buffers.

• maintenance_work_mem is a memory setting used for maintenance tasks. The

default value is 64MB. Setting a large value helps in tasks like VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.

• synchronous_commit This is a trade-off between performance and reliability.

If your application is designed such that performance is more important than the reliability, then turn off synchronous_commit

• checkpoint_timeout, checkpoint_completion_target

11

Security definition (more about users/roles/grants)

First limitation coming from files:

• pg_hba.conf
• pg_ident.conf

Roles are the key as Oracle and other relational DB (not MySQL until 8) Define a role -> assign privileges -> create User and assign Role(s)

CREATE ROLE "user_write"; GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA windmills TO "user_write"; CREATE USER marco WITH ENCRYPTED PASSWORD ‘xxxx'; GRANT "user_write" TO marco;

12

Security definition (more about users/roles/grants)

Privileges:

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• REFERENCES Allows creation of a foreign key constraint referencing the specified table, or specified

column(s) of the table. (See the CREATE TABLE statement.)

• TRIGGER Allows the creation of a trigger on the specified table. (See the CREATE TRIGGER statement.)
• CREATE
• CONNECT Allows the user to connect to the specified database. This privilege is checked at connection

startup (in addition to checking any restrictions imposed by pg_hba.conf).

• TEMPORARY TEMP Allows temporary tables to be created while using the specified database.
• EXECUTE
• USAGE For procedural languages, allows the use of the specified language for the creation of functions in

that language. This is the only type of privilege that is applicable to procedural languages.

• ALL PRIVILEGES

13

Create …database? What is a database?

Create DATABASE (container) VS MySQL (Schema) Create a schema Create tables Auto increment (seq) Triggers Functions Table spaces

14

Backup

This is similar to mysqldump to get the schema but no user definition pg_dump -h 192.168.1.81 -C -Fp -v -o -s -n windmills -c -d test -f /tmp/dump1 -U postgres Restore of clear text via psql if compressed dir via pg_restore

Full backup

pg_basebackup -h 192.168.1.81 -v -X stream -Fp -c fast -R

• P -U postgres -D /tmp/pgbackup

TAR

pg_basebackup -h 127.0.0.1 -U postgres -p 5432 –D /var/lib/pgsql/pg_bakcup -Ft -z -Xf -P -R -l firstbackup

15

Replication

What a fun!! Asynchronous Synchronous Combination of the two MASTER à SYNCà Candidate Master à ASYNC à SLAVE Delay as well Slaves are read-only by default Easy to setup based on WAL Can be logical and replicate only part of the whole server or Streaming and replicate all Now stop/start WAL replay do not works as you may expect in MySQL. In PG you can stop /start with pg_wal_replay_pause()/pg_wal_replay_resume()

16

Replication 2/4

what is needed Basic to set async/sync stream replication: ALTER SYSTEM SET wal_level TO 'replica'; (v10) ALTER SYSTEM SET archive_mode TO 'ON'; ALTER SYSTEM SET max_wal_senders TO '20'; ALTER SYSTEM SET wal_keep_segments TO '100'; <--- calculate on the base u prefer ALTER SYSTEM SET listen_addresses TO '*'; ALTER SYSTEM SET hot_standby TO 'ON'; ALTER SYSTEM SET archive_command TO 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'; ALTER SYSTEM SET wal_log_hints TO 'ON'; ALTER SYSTEM SET log_connections TO 'ON'; ALTER SYSTEM SET synchronous_standby_names = 'node3,node2'; <--- if SYNC Add an entry to pg_hba.conf of Master to allow Replication connections from Slave. local replication all md5 host replication all 127.0.0.1/32 md5 host replication all 192.168.1.1/24 md5

17

Replication 3/4

[root@pg1h3p83 log]# cat ../recovery.conf standby_mode = 'on' primary_conninfo = 'application_name=node3 user=replica password=replica connect_timeout=10 host=192.168.1.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any' #recovery_min_apply_delay='1h' trigger_file='/var/lib/pgsql/10/data/failover.txt' restore_command = 'cp /mnt/server/archivedir/%f %p' archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r' <-- if u do this the file will not be available anymore

18

Replication 4/4

Finally to NOT replicate a table at all: ALTER TABLE table_name SET UNLOGGED ;

• More work in the configuration comparing MySQL but more thing out as

well.

• Async / Sync easy and native
• Not a multi Master solution (out of the box)
• Nodes in replica automatically not writable
• Less chance to make a mess writing on a slave

19

The magic difference in the indexes

The last thing that was surprising me was the quantity of different indexes :

• B-Tree
• Generalized Inverted Index (GIN)
• Generalized Inverted Search Tree (GiST)
• Space-Partitioned GiST (SP-GiST)
• Block Range Indexes (BRIN)
• Hash

All there ready to be used

20

The magic difference in the indexes 2

• B-Tree: For most datatypes and queries
• GIN: For JSONB/hstore/arrays
• GiST: For full-text search and geospatial datatypes
• SP-GiST: For larger datasets with natural but uneven clustering
• BRIN: For really large datasets that line up sequentially
• Hash: For equality operations, and generally B-Tree is still what you want

here

21

There is more?

Tons …

• Extensions
• Foreign data wrappers
• Full Text
• .. And more

I had just scratch the surface and I like it

22

Conclusions

In the spirit use the right tool for the job

• If you need to have a platform more tight with application logic
• If you have need of procedure and complex logic
• If you are close to Oracle approach more than LAMP
• If you are looking to a solution RDBA but that you can expand (ie FDW)

You are looking for Postgres

23

Q&A

24

Thank You Sponsors!!

25

Rate My Session