Re: slow queries on system tables
these values were collected before vacuum analyse:select (select reltuples
from pg_Class where relName = t.relName), * from pg_stat_sys_tables t where
schemaname = 'pg_catalog' and relname in
('pg_class','pg_attribute','pg_index');
reltuples relid schemaname relname seq_scanseq_tup_read
idx_scan
idx_tup_fetch n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd
n_live_tup
n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum
last_analyze
last_autoanalyzevacuum_countautovacuum_countanalyze_count
autoanalyze_count
930475 1249pg_catalog pg_attribute22462 61582178
646833988 2785143399
25381 776 15287 566 10094 15497 41444 NULLNULLNULL
NULL0 0 0 0
169186 1259pg_catalog pg_class5541140 39631813757
638041068 2316793709
224375187 625 44008 169891 56457435NULL2019-08-22
19:58:48.497317 NULL
2019-08-22 13:03:02.770579 0 415 0 1
87387 2610pg_catalog pg_index5263825 33638625440
246528384 590160274 1142
0 208 0 934 208 1350NULLNULLNULLNULL
0 0 0 0
Then ... vacuum analyze pg_catalog.pg_index;vacuum analyze
pg_catalog.pg_attribute;vacuum analyze pg_catalog.pg_class;
reltuples relid schemaname relname seq_scanseq_tup_read
idx_scan
idx_tup_fetch n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd
n_live_tup
n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum
last_analyze
last_autoanalyzevacuum_countautovacuum_countanalyze_count
autoanalyze_count
940569 1249pg_catalog pg_attribute22550 61582570
646919000 2785430403
25381 776 15287 566 940569 0 0 2019-08-24
09:01:26.334472 NULL2019-08-24
09:01:27.169883 NULL1 0 1 0
150903 1259pg_catalog pg_class5542002 39640547448
63819 2316941739
224375187 625 44008 150903 0 0 2019-08-24
09:01:33.130845 2019-08-22
19:58:48.497317 2019-08-24 09:01:33.441355 2019-08-22 13:03:02.770579
1 415
1 1
88321 2610pg_catalog pg_index5263994 33639781274
246576056 590229532 1142
0 208 0 88321 0 0 2019-08-24 09:01:18.080615
NULL2019-08-24 09:01:18.275907
NULL1 0 1 0
and this is explain before
https://explain.depesz.com/s/fUNB
and after
https://explain.depesz.com/s/1FZR
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: timestamp out of range while casting return value to function's return type
On Sat, Aug 24, 2019 at 12:57:07AM +, Thiemo Kellner wrote: > Call: select utils.get_max_timestamptz(); > -- > > Function > -- > create or replace function GET_MAX_TIMESTAMPTZ() > returns timestamptz > language plpgsql > immutable > -- Include the hosting schema into search_path so that dblink > -- can find the pglogger objects. There is no need to access > -- objects in other schematas not covered with public. > as > $body$ > begin > -- highest timestamps on 64bit lubuntu vanilla PostgreSQL 11.3 > return '294277-01-01 00:59:59.99'::timestamptz; > end; > $body$; Also, but that's a nitpick perhaps not relevant to your use case: This $> psql -d gnumed_v22 -U psql (11.5 (Debian 11.5-1+deb10u1)) gnumed_v22=> select 'infinity'::timestamptz; -[ RECORD 1 ]- timestamptz | infinity gnumed_v22=> is the highest timestamp. (You *can* count the horses in *your* corral but there's always more of them elsewhere ;-) Just so you are aware. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: timestamp out of range while casting return value to function's return type
Hi Karsten Thanks for the infinitly good hint. I remembered the infinity blurredly somewhen this morning, looked it up in the docs and already dumped my functions in favour of the infinity solution. :-) Great, that PostgreSQL has the infinity concept! Thanks Kind regards Thiemo Quoting Karsten Hilbert : On Sat, Aug 24, 2019 at 12:57:07AM +, Thiemo Kellner wrote: Call: select utils.get_max_timestamptz(); -- Function -- create or replace function GET_MAX_TIMESTAMPTZ() returns timestamptz language plpgsql immutable -- Include the hosting schema into search_path so that dblink -- can find the pglogger objects. There is no need to access -- objects in other schematas not covered with public. as $body$ begin -- highest timestamps on 64bit lubuntu vanilla PostgreSQL 11.3 return '294277-01-01 00:59:59.99'::timestamptz; end; $body$; Also, but that's a nitpick perhaps not relevant to your use case: This $> psql -d gnumed_v22 -U psql (11.5 (Debian 11.5-1+deb10u1)) gnumed_v22=> select 'infinity'::timestamptz; -[ RECORD 1 ]- timestamptz | infinity gnumed_v22=> is the highest timestamp. (You *can* count the horses in *your* corral but there's always more of them elsewhere ;-) Just so you are aware. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Handys: +41 78 947 36 21 | +49 1578 772 37 37 Tox-Id: B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
Re: Permission for not Django app to do Write and Read
On Sat, Aug 24, 2019 at 11:53 AM Peter Wainaina wrote: > > Thanks much for the response. This is what I mean am a database administrator > for a production company and the product owner doesn't want me to be able to > either read or write information that will come from Django application. I personally hate this kind of setup, because it does not make any sense to me that a developer must setup an application that must interact with a database that the developer himself cannot interact with. However, keep it simple: define a django user, assign each object to such user, revoke any permission from public. And then let's the product owner setup a password. Or look at pgcrypto, because the ony reliable way to deal with "don't look at my data" setup is cryptography. Luca
Postgres SQLSTATE[08006] [7] timeout expired
I have three servers behind a load balancer and a fourth server solely for
Postgres 10 database that is not behind the load balancer. All four are behind
the same firewall, with port 5432 open.
I have a simple browser-based html form to submit email addresses, usernames
and passwords; the form submits through a jQuery program that calls a php
program.
I can navigate the browser to the IP address of the server that hosts the
Postgres database and submit records to the database with no problems. When I
navigate to the site's URL (which goes through the load balancer), I always get
"SQLSTATE[08006] [7] timeout expired" after a short delay -- in other words, no
connection.
The php program is very simple:
'xxx.xx.xx.xx',
'port' => '5432',
'dbname' => '[DBNAME]',
'user' => '[USERNAME]',
'password' => '[PASSWORD]',
]);
echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
echo PHP_EOL;
echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
echo PHP_EOL;
try{
// create a PostgreSQL database connection
echo "I'm here now";
echo PHP_EOL;
//ini_set("default_socket_timeout", 300);
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
// display a message if connected to the PostgreSQL successfully
if($pdo){
echo "Connected to the database successfully!";
echo PHP_EOL;
}
}catch (PDOException $e){
// report error message
echo $e->getMessage();
exit();
}
$pdo = null;
?>
The echo messages are simply debugging progress messages for the browser's dev
console.
The pg_hba.conf has these lines enabled:
pg_hba.conf:
hostall [username] 0.0.0.0/0 trust
hostall all 0.0.0.0/0 md5
hostall all ::/0 md5
hostall all allmd5
The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always comes
back with the address of the load balancer.
I'm new to Postgres, so any help with this connection problem will be very
appreciated. Thanks a lot.
Re: Postgres SQLSTATE[08006] [7] timeout expired
> On Aug 24, 2019, at 4:42 PM, Howard Wells wrote:
>
> I have three servers behind a load balancer and a fourth server solely for
> Postgres 10 database that is not behind the load balancer. All four are
> behind the same firewall, with port 5432 open.
>
> I have a simple browser-based html form to submit email addresses, usernames
> and passwords; the form submits through a jQuery program that calls a php
> program.
>
> I can navigate the browser to the IP address of the server that hosts the
> Postgres database and submit records to the database with no problems. When
> I navigate to the site's URL (which goes through the load balancer), I always
> get "SQLSTATE[08006] [7] timeout expired" after a short delay -- in other
> words, no connection.
>
> The php program is very simple:
>
> echo 'Hello ' . htmlspecialchars($_POST["firstname"]) . '!' ;
> echo PHP_EOL;
>
> $dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
> 'host' => 'xxx.xx.xx.xx',
> 'port' => '5432',
> 'dbname' => '[DBNAME]',
> 'user' => '[USERNAME]',
> 'password' => '[PASSWORD]',
> ]);
>
> echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
> echo PHP_EOL;
>
> echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
> echo PHP_EOL;
>
> try{
> // create a PostgreSQL database connection
>
> echo "I'm here now";
> echo PHP_EOL;
>
> //ini_set("default_socket_timeout", 300);
> $pdo = new PDO($dsn);
> $pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
>
> // display a message if connected to the PostgreSQL successfully
> if($pdo){
> echo "Connected to the database successfully!";
> echo PHP_EOL;
> }
> }catch (PDOException $e){
> // report error message
> echo $e->getMessage();
> exit();
> }
>
> $pdo = null;
>
> ?>
>
> The echo messages are simply debugging progress messages for the browser's
> dev console.
>
> The pg_hba.conf has these lines enabled:
>
> pg_hba.conf:
> hostall [username] 0.0.0.0/0 trust
> hostall all 0.0.0.0/0 md5
> hostall all ::/0 md5
> hostall all allmd5
>
> The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always comes
> back with the address of the load balancer.
>
> I'm new to Postgres, so any help with this connection problem will be very
> appreciated. Thanks a lot.
>
>
Which load balancer and what are its configs?
Is this strictly accurate? "I can navigate the browser to the IP address of the
server that hosts the Postgres database”. i.e. Something is listening on port
80 of the database machine? Or are you sending sql statements through the
firewall directly to the db?
Re: Postgres SQLSTATE[08006] [7] timeout expired
On 8/24/19 3:42 PM, Howard Wells wrote:
I have three servers behind a load balancer and a fourth server solely
for Postgres 10 database that is not behind the load balancer. All four
are behind the same firewall, with port 5432 open.
I have a simple browser-based html form to submit email addresses,
usernames and passwords; the form submits through a jQuery program that
calls a php program.
I can navigate the browser to the IP address of the server that hosts
the Postgres database and submit records to the database with no
problems. When I navigate to the site's URL (which goes through the
load balancer), I always get "SQLSTATE[08006] [7] timeout expired" after
a short delay -- in other words, no connection.
The php program is very simple:
'xxx.xx.xx.xx',
'port' => '5432',
'dbname' => '[DBNAME]',
'user' => '[USERNAME]',
'password' => '[PASSWORD]',
]);
echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
echo PHP_EOL;
echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
echo PHP_EOL;
try{
// create a PostgreSQL database connection
echo "I'm here now";
echo PHP_EOL;
//ini_set("default_socket_timeout", 300);
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
// display a message if connected to the PostgreSQL successfully
if($pdo){
echo "Connected to the database successfully!";
echo PHP_EOL;
}
}catch (PDOException $e){
// report error message
echo $e->getMessage();
exit();
}
$pdo = null;
?>
The echo messages are simply debugging progress messages for the
browser's dev console.
The pg_hba.conf has these lines enabled:
pg_hba.conf:
host all [username] 0.0.0.0/0 trust
host all all 0.0.0.0/0 md5
host all all ::/0 md5
host all all all md5
The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always
comes back with the address of the load balancer.
I'm new to Postgres, so any help with this connection problem will be
very appreciated. Thanks a lot.
In addition to what Rob suggested, what do you see or not in the
Postgres logs when you try the connections(both direct to db and to load
balancer).
--
Adrian Klaver
adrian.kla...@aklaver.com
