Re: Access issue for system queries
Ok, I'll check it out. Thank you. On Sat, 30 Mar, 2024, 10:36 Julien Rouhaud, wrote: > On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath > wrote: > > > > I have granted access to pg_read_all_stats and pg_read_allsettings to > user..still they are not able to receive results from this query.its > empty..we can run SELECT * FROM pg_stat_statements alone..but not below > statement..what could be the reason? > > > > WITH statements AS ( > > SELECT * FROM pg_stat_statements pss > > JOIN pg_roles pr ON (userid=oid) > > WHERE rolname = current_user > > ) > > SELECT calls, > >min_exec_time, > >max_exec_time, > >mean_exec_time, > >stddev_exec_time, > >(stddev_exec_time/mean_exec_time) AS coeff_of_variance, > >query > > FROM statements > > WHERE calls > 500 > > AND shared_blks_hit > 0 > > ORDER BY mean_exec_time DESC > > LIMIT 10 > > Probably because your current user didn't run any query more than 500 > times? Or maybe because you have some other tools that calls > pg_stat_statements_reset() frequently enough. >
Re: Access issue for system queries
On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath wrote: > > I have granted access to pg_read_all_stats and pg_read_allsettings to > user..still they are not able to receive results from this query.its > empty..we can run SELECT * FROM pg_stat_statements alone..but not below > statement..what could be the reason? > > WITH statements AS ( > SELECT * FROM pg_stat_statements pss > JOIN pg_roles pr ON (userid=oid) > WHERE rolname = current_user > ) > SELECT calls, >min_exec_time, >max_exec_time, >mean_exec_time, >stddev_exec_time, >(stddev_exec_time/mean_exec_time) AS coeff_of_variance, >query > FROM statements > WHERE calls > 500 > AND shared_blks_hit > 0 > ORDER BY mean_exec_time DESC > LIMIT 10 Probably because your current user didn't run any query more than 500 times? Or maybe because you have some other tools that calls pg_stat_statements_reset() frequently enough.
Access issue for system queries
Dear all, I have granted access to pg_read_all_stats and pg_read_allsettings to user..still they are not able to receive results from this query.its empty..we can run SELECT * FROM pg_stat_statements alone..but not below statement..what could be the reason? WITH statements AS ( SELECT * FROM pg_stat_statements pss JOIN pg_roles pr ON (userid=oid) WHERE rolname = current_user ) SELECT calls, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, (stddev_exec_time/mean_exec_time) AS coeff_of_variance, query FROM statements WHERE calls > 500 AND shared_blks_hit > 0 ORDER BY mean_exec_time DESC LIMIT 10 Regards, Arun
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
On 2024-03-30 05:53, Alexander Farber wrote: I use the following postgresql.conf in my Dockerfile ( the full version at https://stackoverflow.com/a/78243530/165071 ), when loading a 28 GByte large europe-latest.osm.pbf Is anybody please able to spot any improvements I could apply to the postgresql.conf config values at the top of my mail, that could reduce the loading time of almost 2 hours? Not specific conf file improvements, but for an initial data load have you done things like turning off fsync(), deferring index creating until after the data load finishes, and that kind of thing? You don't want fsync() off when you're using the database in production, but for long data load scenarios it seems like it'd be a decent fit. With .pbf files, from skimming over how they're described here: https://wiki.openstreetmap.org/wiki/PBF_Format ... they don't seem to be optimised for loading into a database. (?) It kind of looks like they'd be stored into individual records, which probably means they'd be getting imported as individual INSERT statements rather than something that's optimised for bulk loading. :( Regards and best wishes, Justin Clift
Re: How to reference a DB with a period in its name ?
On 3/29/24 15:36, David Gauthier wrote: Ya, I kind of agree on the >1 DB connections not allowed. It (perl/DBI) does allow for >1 active DB handles (objects). But of course those handles/objects have different names and that's how to work with the different ones (not a DB prefix like what I have). Still, it accepted the name in the syntax if it didn't have a ".". I kinda liked the db prefix because I'm doing these sweeping deletes and I wanted to make extra-sure that I was deleting from the correct DB. I'm going to ask them to just create another DB without the "." in the name. All the code has the prefix and I don't want to uproot that (because it's working). Or is it working, really? Or are you working around it?
Re: PostgreSQL hardening best practice
Hi Jason, CIS benchmark is a good place to that. https://www.cisecurity.org/benchmark/postgresql Regards, Flavious On Fri, Mar 29, 2024 at 5:37 PM Jason Long wrote: > Hello, > There are many articles on the internet to secure PostgreSQL, but which > one is valid and up-to-date! > > Please introduce a suitable article. > > Thank you. >
Re: How to reference a DB with a period in its name ?
Hi: I think your problem is in the Perl side more than in PostgreSQL's. When you said you tried escaping with \, Did you mean *exactly* this? "delete from \"thedb.v1\".theschem.thetab where col1 = 1" I think this should work. Or you can use single quotes to enclose the query, so that you don't need to escape the double quotes: 'delete from "thedb.v1".theschem.thetab where col1 = 1' Best regards. On 29/03/2024 22:16, David Gauthier wrote: Hi: I have a DB named "thedb", which has a schema called "theschem" which has a table called "thetab". In a perl/DBI script, I am able to work with the table by referencing it as... "thedb.theschem.thetab" as in... "delete from thedb.theschem.thetab where col1=1" No problem (so far...) New DB now has a name "thedb.v1" (notice the "."), the schem and table are the same as above. But now I'm getting a syntax error presumably because of that "." introduced in the DB name. I tried encapsulating the DB name in double quotes (no good), single quotes (still no good) escaping with '\' (no good), escaping with ".." (no good). Is there a way I can reference the DB which has the "." in it's name ? v11.5 on linux (I know, way past time to upgrade) Thanks in Advance !
PostgreSQL hardening best practice
Hello,There are many articles on the internet to secure PostgreSQL, but which one is valid and up-to-date! Please introduce a suitable article. Thank you.
Re: How to reference a DB with a period in its name ?
Ya, I kind of agree on the >1 DB connections not allowed. It (perl/DBI) does allow for >1 active DB handles (objects). But of course those handles/objects have different names and that's how to work with the different ones (not a DB prefix like what I have). Still, it accepted the name in the syntax if it didn't have a ".". I kinda liked the db prefix because I'm doing these sweeping deletes and I wanted to make extra-sure that I was deleting from the correct DB. I'm going to ask them to just create another DB without the "." in the name. All the code has the prefix and I don't want to uproot that (because it's working). Thanks David ! On Fri, Mar 29, 2024 at 5:22 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Fri, Mar 29, 2024 at 2:16 PM David Gauthier >> wrote: >> >>> I tried encapsulating the DB name in double quotes (no good) >>> >> >> This is what the documentation says you are supposed to do for non-simple >> identifiers so you need to show your work to understand where you went >> wrong. >> >> > Actually, you really aren't supposed to put the database name in there at > all; it's pointless since there is no ability to reference a database other > than the one you are connected to. > > David J. > >
Re: How to reference a DB with a period in its name ?
> On Mar 29, 2024, at 14:16, David Gauthier wrote > I tried encapsulating the DB name in double quotes (no good), single quotes > (still no good) escaping with '\' (no good), escaping with ".." (no good). This is probably more about the string handling in the API you are using than PostgreSQL per se, because enclosing the individual components in double quotes does indeed work: x.y=# select * from "x.y".s.t; i --- (0 rows)
Re: How to reference a DB with a period in its name ?
On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Mar 29, 2024 at 2:16 PM David Gauthier > wrote: > >> I tried encapsulating the DB name in double quotes (no good) >> > > This is what the documentation says you are supposed to do for non-simple > identifiers so you need to show your work to understand where you went > wrong. > > Actually, you really aren't supposed to put the database name in there at all; it's pointless since there is no ability to reference a database other than the one you are connected to. David J.
Re: How to reference a DB with a period in its name ?
On Fri, Mar 29, 2024 at 2:16 PM David Gauthier wrote: > I tried encapsulating the DB name in double quotes (no good) > This is what the documentation says you are supposed to do for non-simple identifiers so you need to show your work to understand where you went wrong. David J.
How to reference a DB with a period in its name ?
Hi: I have a DB named "thedb", which has a schema called "theschem" which has a table called "thetab". In a perl/DBI script, I am able to work with the table by referencing it as... "thedb.theschem.thetab" as in... "delete from thedb.theschem.thetab where col1=1" No problem (so far...) New DB now has a name "thedb.v1" (notice the "."), the schem and table are the same as above. But now I'm getting a syntax error presumably because of that "." introduced in the DB name. I tried encapsulating the DB name in double quotes (no good), single quotes (still no good) escaping with '\' (no good), escaping with ".." (no good). Is there a way I can reference the DB which has the "." in it's name ? v11.5 on linux (I know, way past time to upgrade) Thanks in Advance !
Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Good evening, I use the following postgresql.conf in my Dockerfile ( the full version at https://stackoverflow.com/a/78243530/165071 ), when loading a 28 GByte large europe-latest.osm.pbf into PostgreSQL 16 with PostGIS extension: echo "shared_buffers = 1GB">> $PGDATA/postgresql.conf && \ echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \ And after/during osm2pgsql is loading the data into the database, the following messages are displayed by PostgreSQL: 2024-03-29T14:50:19.6909027Z 2024-03-29 14:50:19Processed 3355283586 nodes in 432s (7m 12s) - 7767k/s 2024-03-29T14:50:19.6909478Z 2024-03-29 14:50:19Processed 406646166 ways in 2720s (45m 20s) - 150k/s 2024-03-29T14:50:19.6909785Z 2024-03-29 14:50:19Processed 7265827 relations in 672s (11m 12s) - 11k/s 2024-03-29T14:50:20.0292733Z [0m [91m2024-03-29 14:50:20 node cache: stored: 3355283586(100.00%), storage efficiency: 56.37% (dense blocks: 268264, sparse nodes: 1877553733), hit rate: 100.00% 2024-03-29T14:50:20.0293430Z 2024-03-29 14:50:20 Clustering table 'planet_osm_line' by geometry... 2024-03-29T14:50:20.0293787Z 2024-03-29 14:50:20 Clustering table 'planet_osm_point' by geometry... 2024-03-29T14:50:20.1056438Z [0m [91m2024-03-29 14:50:20 Clustering table 'planet_osm_polygon' by geometry... 2024-03-29T14:50:20.1056819Z 2024-03-29 14:50:20 Clustering table 'planet_osm_roads' by geometry... 2024-03-29T15:15:27.3834184Z [0m [91m2024-03-29 15:15:24 Creating geometry index on table 'planet_osm_roads'... 2024-03-29T15:15:56.7850293Z [0m2024-03-29 15:15:56.784 UTC [344] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:16:56.7864755Z 2024-03-29 15:16:56.785 UTC [347] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:17:56.7891503Z 2024-03-29 15:17:56.788 UTC [350] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:18:16.5830638Z [91m2024-03-29 15:18:16 Analyzing table 'planet_osm_roads'... 2024-03-29T15:18:18.1485666Z [0m [91m2024-03-29 15:18:18 All postprocessing on table 'planet_osm_roads' done in 1678s (27m 58s). 2024-03-29T15:22:17.1463669Z [0m [91m2024-03-29 15:22:17 Creating geometry index on table 'planet_osm_point'... 2024-03-29T15:22:56.8860848Z [0m2024-03-29 15:22:56.885 UTC [365] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:23:56.8890669Z 2024-03-29 15:23:56.888 UTC [368] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:24:56.9521355Z 2024-03-29 15:24:56.951 UTC [371] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:25:56.9166127Z 2024-03-29 15:25:56.915 UTC [374] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:26:57.4606148Z 2024-03-29 15:26:57.460 UTC [377] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:27:56.9555832Z 2024-03-29 15:27:56.955 UTC [380] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:28:56.9718398Z 2024-03-29 15:28:56.971 UTC [383] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:29:56.9912768Z 2024-03-29 15:29:56.990 UTC [386] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:30:57.1482580Z 2024-03-29 15:30:57.147 UTC [389] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:31:01.2674499Z [91m2024-03-29 15:31:01 Analyzing table 'planet_osm_point'... 2024-03-29T15:31:02.3310950Z [0m [91m2024-03-29 15:31:02 All postprocessing on table 'planet_osm_point' done in 2442s (40m 42s). 2024-03-29T15:41:13.1544481Z [0m [91m2024-03-29 15:41:13 Creating geometry index on table 'planet_osm_line'... 2024-03-29T15:41:57.2434689Z [0m2024-03-29 15:41:57.242 UTC [422] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:42:57.2519273Z 2024-03-29 15:42:57.251 UTC [425] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:43:57.2626514Z 2024-03-29 15:43:57.262 UTC [428] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:44:57.2531401Z 2024-03-29 15:44:57.252 UTC [431] LOG: skipping vacuum of "planet_osm_line" --- lock not available 20
