Re: postgresql systemd service fails to start only on boot but not manually
On Sat, Sep 22, 2018 at 07:14:33AM -0700, Adrian Klaver wrote:
>
> Linux distro and version?
Arch Linux
$ uname -a
Linux vps 4.18.9-arch1-1-ARCH #1 SMP PREEMPT Wed Sep 19 21:19:17 UTC
2018 x86_64 GNU/Linux
It's a VPS with one CPU core on it and 2G RAM - not very much I know,
hosted on OVH
>
> Assuming Postgres version 10+ given logical replication warning. Still
> actual version would be nice.
>
> How did you install Postgres?
With the package manager and the files used in it's build are viewable
from here:
https://git.archlinux.org/svntogit/packages.git/tree/trunk?h=packages/postgresql
>
> Where did systemd script come from?
>
> What is in the systemd script?
The systemd service was installed with the package
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=notify
TimeoutSec=120
User=postgres
Group=postgres
Environment=PGROOT=/var/lib/postgres
SyslogIdentifier=postgres
PIDFile=/var/lib/postgres/data/postmaster.pid
RuntimeDirectory=postgresql
RuntimeDirectoryMode=755
ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart=/usr/bin/postgres -D ${PGROOT}/data
ExecReload=/bin/kill -HUP ${MAINPID}
KillMode=mixed
KillSignal=SIGINT
# Due to PostgreSQL's use of shared memory, OOM killer is often
overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200
# Additional security-related features
PrivateTmp=true
ProtectHome=true
ProtectSystem=full
NoNewPrivileges=true
[Install]
WantedBy=multi-user.target
>
> What does the log show when you do a successful manual start?
2018-09-22 09:38:44.470 CEST [15251] LOG: listening on IPv4 address
"127.0.0.1", port 5432
2018-09-22 09:38:44.472 CEST [15251] LOG: listening on Unix socket
"/run/postgresql/.s.PGSQL.5432"
2018-09-22 09:38:44.485 CEST [15253] LOG: database system was shut
down at 2018-09-21 20:49:10 CEST
2018-09-22 09:38:44.490 CEST [15251] LOG: database system is ready to
accept connections
>
> What does the system log show when the Postgres reboot startup fails?
This was posted in my 1st message.
RE: PostgreSQl, PHP and IIS
Thanks for the suggestions. I don't think there is any dependencies in the lib folder, but added it anyway and it made no difference. I tried "Process Explorer" (having previously tried "Dependency Walker") PE revealed one or two more dependencies I was previously unaware of. Added these to the root PHP folder, but still got me no further. I have even added Postgres.EXE to the php root folder. In desperation I tried adding all the postgres files to the PHP Ext folder. Unsurprisingly that did not yield results either. Does anyone know of a step by step guide for installing php and postgres on windows server? So I can doublecheck to see if there is anything I have missed. I can only assume I have made some simple mistake as it surely can't be this difficult to set up. Cheers, Mark __ -Original Message- From: Adrian Klaver Sent: 22 September 2018 16:45 To: Mark Williams ; pgsql-general@lists.postgresql.org Subject: Re: PostgreSQl, PHP and IIS On 9/22/18 3:06 AM, Mark Williams wrote: > "Fatal error: Call to undefined function pg_connect". > > Obviously, that is to be expected if pgsql module is not loading. Went back over your previous post and saw: "I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. " I would say add the lib/ of the Postgres install to the search path. > > __ > > -Original Message- > From: Adrian Klaver > Sent: 21 September 2018 23:02 > To: Mark Williams ; > pgsql-general@lists.postgresql.org > Subject: Re: PostgreSQl, PHP and IIS > > On 9/21/18 10:35 AM, Mark Williams wrote: >> I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS. >> >> I am trying to get this to work following any advice I have been able >> to find on the web, but with no success. My configuration so far is >> as > below. >> >> PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll. >> >> Both these dlls are located in the correct extensions directory >> according to phpinfo. >> >> According to phpinfo the Configuration File Path is "C:\Windows" and >> the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". >> I have the same php.ini file at both locations. >> >> I have copied the version of libpq.dll from the lib folder of the >> postgresql installation to the root folder of the PHP installation. >> >> I have added the bin folder of the postgresql installation to the >> Windows system search path. >> >> Phpinfo shows that pdo_pgsql extension is loaded but not pgsql. >> >> I have also tried copying the dependent dlls (ssleay32.dll, >> libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin >> folder to the php root folder). >> >> Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an >> undefined function call error is displayed by php when I try and run >> any pg php functions. >> >> Can anyone please shed any light on what more I need to do? > > So what is the error you are getting? > > Or to put it another way, what is not happening? > >> >> Regards, >> >> Mark >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Adrian Klaver adrian.kla...@aklaver.com
Re: postgresql systemd service fails to start only on boot but not manually
On Sat, Sep 22, 2018 at 04:58:18PM +0200, Christoph Moench-Tegeder wrote: > ## Doron Behar (doron.be...@gmail.com): > > > My server fails to start PostgreSQL only on boot, if I restart it > > manually afterwards it doesn't have any problem starting. Here is the > > log extracted from the journal: > > > > ``` > > 2018-09-21 20:46:40.028 CEST [306] LOG: listening on IPv4 address > > "127.0.0.1", port 5432 > > 2018-09-21 20:46:40.036 CEST [306] LOG: listening on Unix socket > > "/run/postgresql/.s.PGSQL.5432" > > 2018-09-21 20:46:40.233 CEST [337] LOG: database system was shut down at > > 2018-09-21 20:46:21 CEST > > 2018-09-21 20:48:10.441 CEST [352] WARNING: worker took too long to start; > > canceled > > 2018-09-21 20:49:10.469 CEST [352] WARNING: worker took too long to start; > > canceled > > This would indicate that your machine is overloaded during start - > perhaps there's just too much being started at the same time? > ObRant: that's what happens if people take "system startup duration" > as a benchmark and optimize for that - sure, running one clumsy shell > script after another isn't effective usage of today's systems, > but starting eight dozens programs all at once may have other > side effects. Really, with the hardware taking small ages to find > it's own arse before even loading the boot loader, those few seconds > weren't worth optimizing - and if people reboot their computers so > often that startup time takes a measurable toll on their productive > day, perhaps they should rather spend their time thinking about their > usage pattern than "optimizing" the startup process. > > So, now that I've got that off my chest... your machine propably tries to > do too much at the same time when booting: the worker processes take > longer than 90 seconds to start. Slow CPU or storage maybe? > > > 2018-09-21 20:49:10.478 CEST [306] LOG: database system is ready to accept > > connections > > 2018-09-21 20:49:10.486 CEST [306] LOG: received fast shutdown request > > And in the mean time, systemd has lost it's patience, declares the > start as failed and terminates the process group. (The default systemd > timeout is 90 seconds, at least in some releases of systemd, so > this fits quite nicely). > > You could try to work around this by increasing TimeoutStartSec > in postgresql's systemd unit (or even globally), which perhaps > only hides the problem until the next service suddenly doesn't > start anymore. > You could move postgresql to the end of the boot order by > adding "After=..." to the Unit section of the systemd service > file, the value behind "After=" being all the other services in > the same target, which should reduce parallelism and improve > PostgreSQL's startup behaviour. > A more advanced variant of that would be to create a new > systemd target, make that start "After" multiuser.target > or even graphical.target (depending on your setup), make sure > it "Requires" the current default systemd target and make > postgresql the only additional service in that target. > (This would be the cleanest solution, but you should get some > grasp of systemd and how your specific distribution uses it > before meddling with the default targets; I don't know every > distribution/version variant of systemd integration, so I > can't give that specific instructions here). > Or you figure out what the heck your machine is running > during startup any why it is that slow, and try to fix that. > > Regards, > Christoph Thanks for your very detailed answer, that helped me a lot. I've increased `TimeoutSec=` to infinity in the systemd service since it was set initially to 120 seconds which apparently wasn't enough for my poor VPS with 2G RAM and 1 CPU core. That worked great, I still feel like I have slow startups but at least PostgreSQL doesn't totally fail to start on boot. I'll try to debug the slow startups on my own, thanks again for everything! Doron.
How to investigate what postgres is spending time on
Hi
I have some simple INSERT / UPDATE queries, that takes a long time the first
time they are run in out test environment, but I'm not sure what postgres is
doing and what I can do to help it. Whats common is that the table contains
many rows in the order of about 20 millions.
Query:
INSERT INTO communication.request_parameter (request_id,
template_version_parameter_id, parameter_value)
VALUES (1222, 1211, 122) RETURNING request_parameter_id
Row from pg_stat_statements:
---+++--++--++++++++++++++++++-
userid | dbid | queryid| query
| calls | total_time | min_time | max_time |
mean_time | stddev_time| rows | shared_blk | shared_blk
| shared_blk | shared_blk | local_blks | local_blks | local_blks | local_blks |
temp_blks_ | temp_blks_ | blk_read_t | blk_write_ |
---+++--++--++++++++++++++++++-
16385 | 16389 | 2064198912 | INSERT INTO
communication.request_parameter (request_id, tem | 98 | 646.393451
| 0.03 | 638.712758 | 6.59585154081633 | 64.1818799227704 | 98
| 2850 | 24 | 21 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 0 | 0 |
Description of table:
# \d communication.request_parameter
Table
"communication.request_parameter"
Column | Type| Collation | Nullable |
Default
---+---+---+--+---
request_parameter_id | integer | | not null |
nextval('communication.request_parameter_request_parameter_id_seq'::regclass)
request_id| integer | | not null |
template_version_parameter_id | integer | | not null |
parameter_value | character varying | | |
Indexes:
"request_parameter_pkey" PRIMARY KEY, btree (request_parameter_id)
"request_parameter_parameter_value_idx" btree (parameter_value)
"request_parameter_request_id_idx" btree (request_id)
"request_parameter_template_version_parameter_id_idx" btree
(template_version_parameter_id)
Foreign-key constraints:
"request_parameter_request_id_fkey" FOREIGN KEY (request_id) REFERENCES
communication.request(request_id)
"request_parameter_template_version_parameter_id_fkey" FOREIGN KEY
(template_version_parameter_id) REFERENCES
communication.template_version_parameter(template_version_parameter_id)
This only happens in testing, and on a cold bootet database. The test database
is constructed with pg_dump and restore on fresh postgres installation.
Best Regards
Kim Carlsen
Re: How to investigate what postgres is spending time on
On Sun, Sep 23, 2018 at 1:15 PM Kim Rose Carlsen wrote:
> Hi
>
>
> I have some simple INSERT / UPDATE queries, that takes a long time the
> first time they are run in out test environment, but I'm not sure what
> postgres is doing and what I can do to help it. Whats common is that the
> table contains many rows in the order of about 20 millions.
>
>
> Query:
>
> INSERT INTO communication.request_parameter (request_id,
> template_version_parameter_id, parameter_value)
> VALUES (1222, 1211, 122) RETURNING request_parameter_id
>
> Row from pg_stat_statements:
> ---+++--++--++++++++++++++++++-
> userid | dbid | queryid| query
> | calls | total_time | min_time | max_time
> | mean_time | stddev_time| rows | shared_blk |
> shared_blk | shared_blk | shared_blk | local_blks | local_blks | local_blks |
> local_blks | temp_blks_ | temp_blks_ | blk_read_t | blk_write_ |
> ---+++--++--++++++++++++++++++-
> 16385 | 16389 | 2064198912 | INSERT INTO
> communication.request_parameter (request_id, tem | 98 | 646.393451
>| 0.03 | 638.712758 | 6.59585154081633 | 64.1818799227704 | 98
> | 2850 | 24 | 21 | 0 | 0 | 0
> | 0 | 0 | 0 | 0 | 0 | 0
> |
>
> Description of table:
> # \d communication.request_parameter
> Table
> "communication.request_parameter"
> Column | Type| Collation | Nullable
> |
> Default
>
> ---+---+---+--+---
> request_parameter_id | integer | | not null
> |
> nextval('communication.request_parameter_request_parameter_id_seq'::regclass)
> request_id| integer | | not null
> |
> template_version_parameter_id | integer | | not null
> |
> parameter_value | character varying | |
> |
> Indexes:
> "request_parameter_pkey" PRIMARY KEY, btree (request_parameter_id)
> "request_parameter_parameter_value_idx" btree (parameter_value)
> "request_parameter_request_id_idx" btree (request_id)
> "request_parameter_template_version_parameter_id_idx" btree
> (template_version_parameter_id)
> Foreign-key constraints:
> "request_parameter_request_id_fkey" FOREIGN KEY (request_id)
> REFERENCES communication.request(request_id)
> "request_parameter_template_version_parameter_id_fkey" FOREIGN KEY
> (template_version_parameter_id) REFERENCES
> communication.template_version_parameter(template_version_parameter_id)
>
> This only happens in testing, and on a cold bootet database. The test
> database is constructed with pg_dump and restore on fresh postgres
> installation.
>
Sounds like warming up the cache, but still in a test environment you may
want to add auto_explain to your list of preloads and perhaps set it to
dump explain analyze when it hits a certain threshold. Note that while
dumping the query plans has very little overhead, timing the query plan
nodes does impact performance in a negative way.
>
>
> Best Regards
> Kim Carlsen
>
>
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
Re: PostgreSQl, PHP and IIS
On 9/23/18 3:54 AM, Mark Williams wrote: Thanks for the suggestions. I don't think there is any dependencies in the lib folder, but added it anyway and it made no difference. I tried "Process Explorer" (having previously tried "Dependency Walker") PE revealed one or two more dependencies I was previously unaware of. Added these to the root PHP folder, but still got me no further. I have even added Postgres.EXE to the php root folder. In desperation I tried adding all the postgres files to the PHP Ext folder. Unsurprisingly that did not yield results either. Does anyone know of a step by step guide for installing php and postgres on windows server? So I can doublecheck to see if there is anything I have missed. I can only assume I have made some simple mistake as it surely can't be this difficult to set up. Maybe this?: https://docs.microsoft.com/en-us/iis/application-frameworks/install-and-configure-php-on-iis/install-and-configure-php Cheers, Mark __ -Original Message- From: Adrian Klaver Sent: 22 September 2018 16:45 To: Mark Williams ; pgsql-general@lists.postgresql.org Subject: Re: PostgreSQl, PHP and IIS On 9/22/18 3:06 AM, Mark Williams wrote: "Fatal error: Call to undefined function pg_connect". Obviously, that is to be expected if pgsql module is not loading. Went back over your previous post and saw: "I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. " I would say add the lib/ of the Postgres install to the search path. -- Adrian Klaver adrian.kla...@aklaver.com
Help to understand Actual Rows vs Plan Rows from the query planner output
Hello I have some questions related to the query plan output about the planned and actual rows. In the following example: # explain (analyze true, costs true, format yaml) select * from users where lower(city) = 'melanyfort' and lower(state) = 'ohio'; QUERY PLAN - Plan: + Node Type: "Bitmap Heap Scan" + Parallel Aware: false + Relation Name: "users" + Alias: "users" + Startup Cost: 10.78 + Total Cost: 14.80 + Plan Rows: 1 + Plan Width: 73 + Actual Startup Time: 0.155 + Actual Total Time: 0.155 + Actual Rows: 0 + Actual Loops: 1 + Recheck Cond: "((lower((city)::text) = 'melanyfort'::text) AND (lower((state)::text) = 'ohio'::text))"+ Rows Removed by Index Recheck: 0 + Exact Heap Blocks: 0 + Lossy Heap Blocks: 0 + Plans: + - Node Type: "BitmapAnd" + Parent Relationship: "Outer" + Parallel Aware: false + Startup Cost: 10.78 + Total Cost: 10.78 + Plan Rows: 1 + Plan Width: 0 + Actual Startup Time: 0.153 + Actual Total Time: 0.153 + Actual Rows: 0 + Actual Loops: 1 + Plans: + - Node Type: "Bitmap Index Scan" + Parent Relationship: "Member" + Parallel Aware: false + Index Name: "users_lower_idx" + Startup Cost: 0.00 + Total Cost: 4.66 + Plan Rows: 50 + Plan Width: 0 + Actual Startup Time: 0.048 + Actual Total Time: 0.048 + Actual Rows: 1 + Act
Re: heads up on large text fields.
On 09/22/2018 06:00 AM, Andreas Kretschmer wrote: Am 22.09.2018 um 02:28 schrieb Rob Sargent: However, I get into deep dodo when I try redirecting psql output such as select ld from gt.ld\g /tmp/regen.file works for me if i start psql with -t -A -o /path/to/file (pg 10.5, but psql from 11beta3) Regards, Andreas OK, I'm a little slow on the uptake. The few very wide lines (728035 characters) demand that all the other lines be padded and with 132236 lines you end up with a 96G file (with out the smarts provided about).
Re: Help to understand Actual Rows vs Plan Rows from the query planner output
On 09/23/2018 10:21 PM, Arup Rakshit wrote: > Hello I have some questions related to the query plan output about the > planned and actual rows. In the following example: > > # explain (analyze true, costs true, format yaml) select * from users > where lower(city) = 'melanyfort' and lower(state) = 'ohio'; > QUERY PLAN > > > - Plan: > + > Node Type: "Bitmap Heap Scan" > + > Parallel Aware: false > + > Relation Name: "users" > + > Alias: "users" > + > Startup Cost: 10.78 > + > Total Cost: 14.80 > + > Plan Rows: 1 > + > Plan Width: 73 > + > Actual Startup Time: 0.155 > + > Actual Total Time: 0.155 > + > Actual Rows: 0 > + > Actual Loops: 1 > + > Recheck Cond: "((lower((city)::text) = 'melanyfort'::text) AND > (lower((state)::text) = 'ohio'::text))"+ > Rows Removed by Index Recheck: 0 > + > Exact Heap Blocks: 0 > + > Lossy Heap Blocks: 0 > + > Plans: > + > - Node Type: "BitmapAnd" > + > Parent Relationship: "Outer" > + > Parallel Aware: false > + > Startup Cost: 10.78 > + > Total Cost: 10.78 > + > Plan Rows: 1 > + > Plan Width: 0 > + > Actual Startup Time: 0.153 > + > Actual Total Time: 0.153 > + > Actual Rows: 0 > + > Actual Loops: 1 > + > Plans: > + > - Node Type: "Bitmap Index Scan" > + > Parent Relationship: "Member" > + > Parallel Aware: false > + > Index Name: "users_lower_idx" > + > Startup Cost: 0.00 > + > Total Cost: 4.66 > + > Plan Rows: 50 > + > Plan Width: 0 > + > Actual Startup Time: 0.048 > + > Actual Total Time: 0.048
