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

About me Marco “The Grinch” • Former UN, MySQL AB, Pyt hian, Percona • 2 kids, 1 wife • History of Religions; Ski; Snowboard; Scuba Divin g; 2

Learn how to ride the Dolphin 3

How to eat an elephant? 4

My Motto Use the Right Tool for the Job

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

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/ 7

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

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 9

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_concurrenc y 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 10

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; 11

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 12

Create …database? What is a database? Create DATABASE (container) VS MySQL (Schema) Create a schema Create tables Auto increment (seq) Triggers Functions Table spaces 13

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 14

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() 15

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 16

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 17

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 18

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 19

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 20

There is more? Tons … • Extensions • Foreign data wrappers • Full Text • .. And more I had just scratch the surface and I like it 21

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 22

Q&A 23

Thank You Sponsors!! 24

Rate My Session 25