Re: Oracle vs. PostgreSQL - a comment
Sorry for this reply, but I feel it is necessary to make it clear what is reality and what is FUD against Oracle from Paul's e-mails in this thread... (Note: I work for Oracle now, but I've had 20 years experience as multi-platform database consultant) Paul Förster wrote: > Oracle requires 161 additional packages to be installed, many of which are 32-bit packages, for a supposedly 64-bit only software! This results in 150 MB additional disk space needed and swamps the system with 32-bit packages! That is... not a problem. Is it, for real? > The oracle installation process is horrible. > that's why I scripted the whole create database thing, including PDBs, and > their parameters, file paths, etc. For example, my script to create a > container database is 782 lines long, whereas PostgreSQL just needs an > "initdb". And my script to create a PDB still has 277 lines whereas in > PostgreSQL, you can do it with a simple "create database" line. > Although I completely agree that the Oracle installation process is much longer and more complex than PostgreSQL, I disagree with the rest. The CREATE PLUGGABLE DATABASE is also a single line SQL command... The scripts to create a PDB or a PostgreSQL database depend a lot on what do you want to achieve (empty database? specific users or permissions? sanity checks? pre-emptive backup? add to cmdb?) For a new PostgreSQL architecture in the past I have written 230 lines of code to automate the database creation in an existing PostgreSQL cluster. That included setting up application users, hardening the default permissions on the public schema, registering in the CMDB, etc. It is not much code in my opinion and it is done once for all. For a similar project with Oracle Multitenant, the create_pdb.sh was 177 lines of code, including dealing with TDE wallets and CMU authentication. Again, not that much IMO. > Even moving a database to another path is a nightmare as you'd have to > create new controlfiles, etc. With PostgreSQL you just change the PGDATA > variable after moving/copying the whole database cluster and that's it. > Well, if you copy it and want to run both at the same time, you still have > to change the port in postgresql.conf of course. > This is bashing FUD against Oracle or lack of basic Oracle knowledge. Oracle online move, reorganization and patching capabilities are far ahead from PostgreSQL. Online Datafile Movement has existed since 12cR1. 8 years! https://oracle-base.com/articles/12c/online-move-datafile-12cr1 Prior to that, for many years, it was possible to offline, move, rename and online datafiles, either grouped or singularly, without stopping the instance. Online logs can be rotated to a new location online. The only exception are the controlfiles that require an ALTER SYSTEM, shutdown, move, startup. PostgreSQL must be stopped in order to move the database to a new path, and if it is to a new filesystem, you need the time for a full copy of the data, unless you do it via backup and recovery to reduce the downtime. > it works well if the length of path+filename does not change. I had bad experiences with this technique if the length changes because controlfiles are binary files unless you alter database backup controlfile to trace as '...'. So, as I said, you need to recreate the controlfile. Again no, you don't need to recreate the controlfile for moving the datafiles , and no: altering binary controlfiles with `sed` is nothing a production DBA would ever do... > The way they handle indexes and updates is also much faster than postgres > and you have to worry less about the structure of your queries with respect > to performance. > and then, some day, a developer approaches a DBA with a query which is > generated and, if printed out in a 11pt. sized font, can fill a billboard > on a street, to optimize it or search for what's wrong with it, or why it > performs so slow... That's usually when I play BOFH because I'm not willing > to debug 10 pages which its creator hasn't even cared to take a look at > first. :-P :-) > The laziness or lack of knowledge of your developers is not a problem with Oracle technology. Still, you can get a "query which is generated and, if printed out in a 11pt. sized font, can fill a billboard on a street", give it to Oracle and get the optimal execution plan 99.9% of the times. And if the execution is not optimal, Statistics Feedback kicks in and tries to produce a better one next time. And if it still fails, you can use hints or produce a trace 10053 and pin-point the reason for the CBO choice and get better statistics (or physical structures) for it. > > Comparing Postgres with Oracle is a bit like comparing a rubber duck you might buy your three year old, with a 30 ton super tanker. > yes, and no. You are right about Oracle having gazillions of features but your comparison is way too drastic. > But be honest: How many features do you actually need? Most people use create table, view, sequence, ind
Re: pgbouncer configuration
huh. you are right. i originally installed pgbouncer with yum but removed with just "yum remove pgbouncer" before following "Building from Git" section at http://www.pgbouncer.org/install.html. i just ran "make uninstall" -> reinstalled with 403 21-04-28 06:58:32 git submodule init 404 21-04-28 06:58:36 git submodule update 405 21-04-28 06:58:45 ./autogen.sh 406 21-04-28 06:59:23 ./configure --with-pam --with-systemd 407 21-04-28 07:02:11 make 408 21-04-28 07:02:16 make install but it still looks like no ldap or pam and same error when starting up. any suggestions on how to completely remove and reinstall with support for pam? On Wed, Apr 28, 2021 at 1:51 AM Laurenz Albe wrote: > On Tue, 2021-04-27 at 19:46 -0500, Chris Stephens wrote: > > I'm trying to run pgbouncer but am having trouble with what looks like a > very simple configuration. > > > > centos 7 > > postgres 12 > > pgbouncer 1.15 > > > > we are already using pam for database auth. pgbouncer was compiled with > --with-pam. there is a /etc/pam.d/pgbouncer config file copied from the one > currently being used for postgres auth. > > > > i can list the whole config file if needed but i get the following when > trying to start pgbouncer up: > > > > [postgres@lsst-pgsql02 ~]$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini > > 2021-04-27 19:37:34.256 CDT [10653] ERROR invalid value "pam" for > parameter auth_type in configuration (/etc/pgbouncer/pgbouncer.ini:118) > > 2021-04-27 19:37:34.256 CDT [10653] FATAL cannot load config file > > > > [postgres@lsst-pgsql02 pgbouncer]$ egrep "auth_type" pgbouncer.ini > > auth_type = pam > > > > any ideas? > > I'd suspect that pgBouncer is not built with PAM support after all. > > Run "ldd" on the executable and see if it links with OpenLDAP. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: pgbouncer configuration
I had to install pam-devel before reinstalling pgbouncer. appears to be working now. thanks for pointing me in the right direction Laurenz! On Wed, Apr 28, 2021 at 7:09 AM Chris Stephens wrote: > huh. you are right. i originally installed pgbouncer with yum but removed > with just "yum remove pgbouncer" before following "Building from Git" > section at http://www.pgbouncer.org/install.html. > > i just ran "make uninstall" -> reinstalled with > > 403 21-04-28 06:58:32 git submodule init > 404 21-04-28 06:58:36 git submodule update > 405 21-04-28 06:58:45 ./autogen.sh > 406 21-04-28 06:59:23 ./configure --with-pam --with-systemd > 407 21-04-28 07:02:11 make > 408 21-04-28 07:02:16 make install > > but it still looks like no ldap or pam and same error when starting up. > any suggestions on how to completely remove and reinstall with support for > pam? > > > > On Wed, Apr 28, 2021 at 1:51 AM Laurenz Albe > wrote: > >> On Tue, 2021-04-27 at 19:46 -0500, Chris Stephens wrote: >> > I'm trying to run pgbouncer but am having trouble with what looks like >> a very simple configuration. >> > >> > centos 7 >> > postgres 12 >> > pgbouncer 1.15 >> > >> > we are already using pam for database auth. pgbouncer was compiled with >> --with-pam. there is a /etc/pam.d/pgbouncer config file copied from the one >> currently being used for postgres auth. >> > >> > i can list the whole config file if needed but i get the following when >> trying to start pgbouncer up: >> > >> > [postgres@lsst-pgsql02 ~]$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini >> > 2021-04-27 19:37:34.256 CDT [10653] ERROR invalid value "pam" for >> parameter auth_type in configuration (/etc/pgbouncer/pgbouncer.ini:118) >> > 2021-04-27 19:37:34.256 CDT [10653] FATAL cannot load config file >> > >> > [postgres@lsst-pgsql02 pgbouncer]$ egrep "auth_type" pgbouncer.ini >> > auth_type = pam >> > >> > any ideas? >> >> I'd suspect that pgBouncer is not built with PAM support after all. >> >> Run "ldd" on the executable and see if it links with OpenLDAP. >> >> Yours, >> Laurenz Albe >> -- >> Cybertec | https://www.cybertec-postgresql.com >> >>
postgres index usage count too high
There is a table in the db, whose index_scan count from pg_stat_all_tables for this table seems to be too high, there are not that many queries being executed against this table. Wondering how this count can be too high. 1. The db is up since 80 days so I assume these are cumulative stats since last startup? 2. Could it be possible that a query is using this table joining other tables, and this table is being probed multiple times in loops. Below is a googled part of the plan showing parallel index only scan happened 5 times for the index. I am assuming something of this sort is happening making the index scan count going too high. Please let me know if that might be the case. -> Parallel Index Only Scan using us_geonames_type_idx on us_geonames (cost=0.43..24401.17 rows=559758 width=4) (actual time=0.036..90.309 rows=447806 loops=5) 1. Is there any other possible explanation for this high count. I see updates do increase this count but there are not those many updates. Inserts and deletes do not seem to touch this counter. -[ RECORD 1 ]---+-- relid | 3029143981 schemaname | myschema relname | mytable seq_scan| 196 seq_tup_read| 2755962642 idx_scan| 4362625959 idx_tup_fetch | 3579773932 n_tup_ins | 93821564 n_tup_upd | 645310 n_tup_del | 0 n_tup_hot_upd | 21288 n_live_tup | 31153237 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | 2021-04-24 05:06:56.481349+00 last_autovacuum | 2021-03-04 00:27:26.705849+00 last_analyze| 2021-04-24 05:07:37.589756+00 last_autoanalyze| 2021-03-04 08:55:32.673118+00 vacuum_count| 69 autovacuum_count| 1 analyze_count | 69 autoanalyze_count | 55 db=> select * from pg_stat_all_indexes where relname = 'mytable' and indexrelname = 'mytable_pkey' order by idx_tup_fetch desc nulls last;-[ RECORD 1 ]-+--- relid | 3029143926 indexrelid| 3029143974 schemaname| myschema relname | mytable indexrelname | mytable_pkey idx_scan | 3806451145 idx_tup_read | 97277555 idx_tup_fetch | 61522 Thanks.
Re: postgres index usage count too high
On Wed, 2021-04-28 at 18:41 -0400, Ayub M wrote: > There is a table in the db, whose index_scan count from pg_stat_all_tables > for this > table seems to be too high, there are not that many queries being executed > against > this table. Wondering how this count can be too high. The table contains cumulative statistics. So you should remember the value, look again in a day or a week and calculate the difference to see how often index scans have been used in that time. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
