Adding AVG to a JOIN

[Alexander Farber]

Hello,

in PostgreSQL 10.3 I run the following query to find top 10 players with
the best ELO rating:

# SELECT
u.elo,
u.uid,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
WHERE u.elo > 1500

-- take the most recent record from words_social (storing user details from
social networks)
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)

-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
AND action IN ('play', 'skip', 'swap', 'resign'))
ORDER BY u.elo DESC
LIMIT 10;

 elo  | uid  |  given   |
photo
--+--+--+
 2078 | 1201 | Roman|
https://lh6.googleusercontent.com/-kNp75NGW6wo/AAI/ABs/QN7rEc17JNc/photo.jpg
 1952 | 2846 | дана |
https://avt-30.foto.mail.ru/mail/dance1011/_avatarbig?1523746018
 1923 | 2808 | Ириша|
https://avt-24.foto.mail.ru/mail/irusy2277/_avatarbig?1518190793
 1788 | 3479 | наталья  | https://avt-5.foto.mail.ru/mail/leutan/_avatarbig
 1749 | 3404 | ♕ OLGA ♕ |
https://avt-30.foto.mail.ru/mail/olgapinsk2/_avatarbig?1484081891
 1733 | 3336 | Надежда  |
https://avt-14.foto.mail.ru/mail/katerenyuk78/_avatarbig?1520366579
 1724 | 1765 | ЕЛЕНА|
https://i.mycdn.me/image?id=805029440389&t=0&plc=API&ts=00&aid=1158060544&tkn=*looACpPtImwclHOmPBfnpuashFk
 1717 | 3091 | андрей   |
https://avt-11.foto.mail.ru/yandex.ru/maimun-11/_avatarbig?1453033064
 1711 | 3000 | Алекс|
https://avt-20.foto.mail.ru/mail/taa113/_avatarbig?1495430756
 1708 | 3991 | Кузнецов |
https://avt-10.foto.mail.ru/inbox/sobaka.58/_avatarbig?1353528572
(10 rows)

The above query works well and quick, but I need to add the average score
per move information to it.

Here is such a query for the best player

# SELECT AVG(score) FROM words_moves WHERE uid = 1201;
 avg
-
 18.4803525523319868

However I am not sure, how to "marry" the 2 queries?

I have tried to add words_moves through another JOIN, but that does not
work:

# SELECT
u.elo,
u.uid,
AVG(m.score),-- how to add the player average score?
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN words_moves m USING (uid)
WHERE u.elo > 1500

AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)

AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
AND action IN ('play', 'skip', 'swap', 'resign'))
ORDER BY u.elo DESC
LIMIT 10
;
ERROR:  42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: u.elo,
^

Please give me some hints, how to approach this.

Thank you!
Alex

P.S: Below are the 3 tables referenced above:

# \d words_users
 Table "public.words_users"
Column |   Type   | Collation | Nullable |
Default
---+--+---+--+--
 uid   | integer  |   | not null |
nextval('words_users_uid_seq'::regclass)
 created   | timestamp with time zone |   | not null |
 visited   | timestamp with time zone |   | not null |
 ip| inet |   | not null |
 fcm   | text |   |  |
 apns  | text |   |  |
 adm   | text |   |  |
 motto | text |   |  |
 vip_until | timestamp with time zone |   |  |
 grand_until   | timestamp with time zone |   |  |
 banned_until  | timestamp with time zone |   |  |
 banned_reason | text |   |  |
 elo   | integer  |   | not null |
 medals| integer  |   | not null |
 coins | integer  |   | not null |
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_banned_reason_check" CHECK (length(banned_reason) > 0)
"words_users_elo_check" CHECK (elo >= 0)
"words_users_medals_check" CHECK (medals >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "

Re: Adding AVG to a JOIN

[Daniel Verite]

Alexander Farber wrote:

> Here is such a query for the best player
> 
> # SELECT AVG(score) FROM words_moves WHERE uid = 1201;
> avg
> -
> 18.4803525523319868
> 
> However I am not sure, how to "marry" the 2 queries?
> 
> I have tried to add words_moves through another JOIN, but that does not
> work:

You may use a correlated subquery in the SELECT clause,
like this:

 SELECT
   u.elo,
   u.uid,
   (SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
   s.given,
   s.photo


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Adding AVG to a JOIN

[Alexander Farber]

Thank you, Daniel -

On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite 
wrote:

>
> You may use a correlated subquery in the SELECT clause,
> like this:
>
>  SELECT
>u.elo,
>u.uid,
>(SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
>s.given,
>s.photo
>

this has worked great for me:

   SELECT
u.elo,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS score,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
WHERE u.elo > 1500
-- take the most recent record from words_social
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
week'
AND action IN ('play', 'skip', 'swap',
'resign'))
ORDER BY u.elo DESC
LIMIT 10

Is that what is called LATERAL JOIN?

Regards
Alex

RE: Strange error in Windows 10 Pro

[Igor Neyman]

On system hard drive search for the file called 
bitrock_installer_.log.
You should find it in Users\\AppData\Local\Temp.
This log should provide you with more specific details explaining why data 
folder is empty.

Regards,
Igor

From: Dale Seaburg [mailto:kg...@verizon.net]
Sent: Saturday, April 21, 2018 4:09 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Strange error in Windows 10 Pro


WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.

Thanks Adrian for the suggestion of running the installer with Admin rights.  
Unfortunately, I get the same results.  It appears that all of the folders 
within C:\Program Files\PostgreSQL\9.6 path are created, and populated, BUT, 
when the items in the data folder are to be created, or copied into, it leaves 
an error message as noted previously.  The data folder is empty.

It's almost as if the PC is missing a critical .dll needed in the data folder 
filling function (my guess).

Again, I am at a loss as to what to do.

Dale

On 4/20/2018 11:13 PM, Dale Seaburg wrote:
Oops, my mistake.  I'll let this serve the list with what I've tried so far.  
Thanks, Adrian for the reminder.

I hope tomorrow to visit the customer and try the Admin user method of 
installing.

Dale


On 4/20/2018 11:03 PM, Adrian Klaver wrote:

On 04/20/2018 07:52 PM, Dale Seaburg wrote:

Please also reply to list.
Ccing list to put it front of more eyes.


Thanks, Adrian, for suggestion(s).


On 4/20/2018 9:35 PM, Adrian Klaver wrote:

On 04/20/2018 07:16 PM, Dale Seaburg wrote:

I am attempting to install a fresh copy of postgresql-9.6.8-2-windows-x86 on a 
new DELL PC with Windows 10 Pro.  It

This was downloaded from where?
downloaded from https://www.postgresql.org/download/windows/, selecting to use 
the installer pointed to near the beginning of that page.  The actual website 
that contained the installer file was: 
"https://www.enterprisedb.com/thank-you-downloading-postgresql?anid=209611";.



gets near the end of the install when the message says it is attempting to 
start the the database server.   There's a long pause, followed by an error 
message: "Failed to load SQL modules into the database cluster". Using File 
Explorer, i notice the 9.6\base\ folder is empty?  Has anyone else seen this 
before?  I have no clue where to look for the issue.

You are running as Admin user?
I can't say that I was.  Will check this next time (maybe tomorrow) when I am 
customer's site.  I didn't even think about right-clicking on the installer and 
selecting run-as-admin.  I know I did not deliberately use Admin user on the 
test PC mentioned below, and it installed with no problems.





As a double-check on a different PC with Windows 10 Pro, I get no error 
message, and the database is installed correctly.

Dale Seaburg

Re: Strange error in Windows 10 Pro

[Moreno Andreo]

Il 21/04/2018 22:35, Adrian Klaver ha scritto:

On 04/21/2018 01:08 PM, Dale Seaburg wrote:
Thanks Adrian for the suggestion of running the installer with Admin 
rights.  Unfortunately, I get the same results.  It appears that all 
of the folders within C:\Program Files\PostgreSQL\9.6 path are 
created, and populated, BUT, when the items in the *data* folder are 
to be created, or copied into, it leaves an error message as noted 
previously. The *data* folder is empty.


It's almost as if the PC is missing a critical .dll needed in the 
*data* folder filling function (my guess).


Again, I am at a loss as to what to do.


Have you looked at the system logs e.g. Event Viewer?


If you look in %temp%", there should be one or more files named 
"bitrock_installer" or something similar (search for "bitrock"), that's 
the setup log with what's went good and what not.
Another hint I had since 9.1 times (it was 2012, I guess) was to avoid 
installing Postgres under system folders (c:\program files, c:\users, 
and so on) because in some cases there could be some nasty behaviors... 
try installing on something like c:\PG96...


HTH
Cheers,
Moreno.-

Using the public schema

[Charlin Barak]

Hi,
We will be developing three new applications in PostgreSQL, each having its
own database instance running on different hosts. We will only have one
schema per Postgres instance. The data is read-write only by one
application/schema in the DB instance and the data is published to other
applications via API calls.

In such a standalone database configuration, are there any security
implications or any downsides to creating the application in the public
schema?

Thanks.

Re: Strange error in Windows 10 Pro

[Daniel Westermann]

Il 21/04/2018 22:35, Adrian Klaver ha scritto: 
> On 04/21/2018 01:08 PM, Dale Seaburg wrote: 
>> Thanks Adrian for the suggestion of running the installer with Admin 
>> rights. Unfortunately, I get the same results. It appears that all 
>> of the folders within C:\Program Files\PostgreSQL\9.6 path are 
>> created, and populated, BUT, when the items in the *data* folder are 
>> to be created, or copied into, it leaves an error message as noted 
>> previously. The *data* folder is empty. 


Did you try to execute the installer with a local admin? We had a similar issue 
when the "admin" user was not a local user but somehow a remote admin (sorry I, 
not an windows expert). When we switched to local admin all was fine and that 
seemed to be related to group policies 

Re: Rationale for aversion to the central database?

[Sven R. Kunze]

So far, I have nothing to add, but just one thing. See below:


On 09.04.2018 00:37, g...@luxsci.net wrote:
One advantage to using logic and functions in  the db is that you can 
fix things immediately without having to make new application builds. 
That in itself is a huge advantage, IMO.


This is actually not the case. You want to have those logic tested as 
thoroughly as possible being so close to your precious data.


So, you write migration code that substitutes the old logic, test the 
whole package, if successful, deploy (and thus run the migration).


Cheers,
Sven

Re: Using the public schema

[Thomas Poty]

Hi charlin,
I invite you to  read this doc it explains very well  the security issue
with the public schéma :
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

The main problem with the public schéma is the default privileges (execute)
granted to all users able to connect.

Personally, each time i create a database, i remove the public schema

Regards
Thomas

Le lun. 23 avr. 2018 à 17:00, Charlin Barak  a
écrit :

> Hi,
> We will be developing three new applications in PostgreSQL, each having
> its own database instance running on different hosts. We will only have one
> schema per Postgres instance. The data is read-write only by one
> application/schema in the DB instance and the data is published to other
> applications via API calls.
>
> In such a standalone database configuration, are there any security
> implications or any downsides to creating the application in the public
> schema?
>
> Thanks.
>
>
>

Re: Adding AVG to a JOIN

[Alexander Farber]

Good evening,

On Mon, Apr 23, 2018 at 12:56 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite 
> wrote:
>
>>
>> You may use a correlated subquery in the SELECT clause,
>>
>
>SELECT
> u.elo,
> (SELECT ROUND(AVG(score), 1) FROM words_moves
> WHERE uid = u.uid) AS score,
> s.given,
> s.photo
> FROM words_users u
> JOIN words_social s USING (uid)
> WHERE u.elo > 1500
> -- take the most recent record from words_social
> AND NOT EXISTS (SELECT 1
> FROM words_social x
> WHERE s.uid = x.uid
> AND x.stamp > s.stamp)
> -- only show players who where active in the last week
> AND EXISTS (SELECT 1
> FROM words_moves
> WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
> week'
> AND action IN ('play', 'skip', 'swap',
> 'resign'))
> ORDER BY u.elo DESC
> LIMIT 10
>


thank you all for helping me in adding an average score per move to my SQL
JOIN.

Of course I would like to add yet another statistic and now am trying to
add the average time per move to the same query, by prepending it a CTE
with LAG():

 WITH cte AS (
SELECT
m.gid,
m.uid,
m.played,
LAG(m.played) OVER (PARTITION BY m.gid
ORDER BY played) AS prev_played
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid AND m.uid in
(g.player1, g.player2))
WHERE m.played > CURRENT_TIMESTAMP - interval '1
month'
)
SELECT
u.elo,
AVG(c.played - c.prev_played) AS avg_time_per_move,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS score,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN cte c USING (uid)
WHERE u.elo > 1500
-- take the most recent record from words_social
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
week'
AND action IN ('play', 'skip', 'swap',
'resign'))
ORDER BY u.elo DESC
LIMIT 10;

but this fails with

ERROR:  42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 15: u.elo,
 ^

And I don't understand why adding a CTE has caused it, because without the
CTE the GROUP BY u.elo was not required...

Regards
Alex

Re: Adding AVG to a JOIN

[David G. Johnston]

On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> SELECT
> u.elo,
> AVG(c.played - c.prev_played) AS avg_time_per_move,
> (SELECT ROUND(AVG(score), 1) FROM words_moves
> WHERE uid = u.uid) AS score,
>
> And I don't understand why adding a CTE has caused it, because without the
> CTE the GROUP BY u.elo was not required...
>
>
​Adding "AVG(c.played - c.prev_played)" directly to the top-level select
statement​ column list is what turned it into a "GROUP BY" query.  When you
embedded the "AVG(score)" in a subquery the GROUP BY was limited to just
that subquery, and it had no other columns besides the aggregate and so
didn't require a GROUP BY clause.

David J.

Re: Postgres and fsync

[Tim Cross]

Andres Freund  writes:

> Hi,
>
> On 2018-04-23 08:30:25 +1000, Tim Cross wrote:
>> the recent article in LWN regarding issues with fsync and error
>> reporting in the Linux kernel and the potential for lost data has
>> prompted me to ask 2 questions.
>
> Note that you need to have *storage* failures for this to
> happen. I.e. your disk needs to die, and there's no raid or such to fix
> the issue.
>
>
>> 1. Is this issue low level enough that it affects all potentially
>> supported sync methods on Linux? For example, if you were concerned
>> about this issue and you had a filesystem which supports open_sync or
>> open_datasync etc, is switching to one of these options something which
>> should be considered or is this issue low level enough that all sync
>> methods are impacted?
>
> No, the issue is largely about datafiles whereas the setting you refer
> to is about the WAL.
>
> Greetings,
>
> Andres Freund

OK, thanks.

-- 
Tim Cross

Re: Strange error in Windows 10 Pro

[Dale Seaburg]

Thanks to Moreno and Igor for the Event Viewer suggestions.  Here are a 
few lines of log file where they differ between a good install and a bad 
incomplete install.  The good install was from my Shop PC with Windows 
10 Pro.


BTW, I had success with another Dell PC with Win 10 Pro about 4 months 
ago.  This problem appears to be a one-off.


Good Install:
/Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the SHOP-PC\Dale account can read the path 
C:\Program Files\PostgreSQL\9.6\data//

//    Executing batch file 'rad0510A.bat'...//
//    processed file: C:\Program Files\PostgreSQL\9.6\data//
//Successfully processed 1 files; Failed processing 0 files/

Bad Install:
/Called AclCheck(D:\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the WINDOWS-6BEGVO1\don king account can 
read the path D:\PostgreSQL\9.6\data//

//    Executing batch file 'rad6DBC7.bat'...//
//
/Notice the last four lines of the *Good Install* vs the last two lines 
of the *Bad Install*.  There is no indication of processing, or whether 
it was successful or not in the bad install.


As you can see, I even took Igor's suggestion to install in a non-system 
(Program Files) path, to no success.


Again, not sure what to do.  Open for suggestions...  I'm almost ready 
to call Dell and complain about a "bad" Win 10 Pro install.


Dale
//
On 4/23/2018 9:54 AM, Moreno Andreo wrote:

Il 21/04/2018 22:35, Adrian Klaver ha scritto:

On 04/21/2018 01:08 PM, Dale Seaburg wrote:
Thanks Adrian for the suggestion of running the installer with Admin 
rights.  Unfortunately, I get the same results.  It appears that all 
of the folders within C:\Program Files\PostgreSQL\9.6 path are 
created, and populated, BUT, when the items in the *data* folder are 
to be created, or copied into, it leaves an error message as noted 
previously. The *data* folder is empty.


It's almost as if the PC is missing a critical .dll needed in the 
*data* folder filling function (my guess).


Again, I am at a loss as to what to do.


Have you looked at the system logs e.g. Event Viewer?


If you look in %temp%", there should be one or more files named 
"bitrock_installer" or something similar (search for "bitrock"), 
that's the setup log with what's went good and what not.
Another hint I had since 9.1 times (it was 2012, I guess) was to avoid 
installing Postgres under system folders (c:\program files, c:\users, 
and so on) because in some cases there could be some nasty 
behaviors... try installing on something like c:\PG96...


HTH
Cheers,
Moreno.-

Re: Strange error in Windows 10 Pro

[Dale Seaburg]

As you may have noticed in the Bad Install section, the user's name was 
'don king' - with an embedded space.  I created a new user 'Dale' to 
test out whether that name might be causing a problem.  The new name 
also failed to complete an install.


Dale


On 4/23/2018 9:09 PM, Dale Seaburg wrote:


Thanks to Moreno and Igor for the Event Viewer suggestions. Here are a 
few lines of log file where they differ between a good install and a 
bad incomplete install.  The good install was from my Shop PC with 
Windows 10 Pro.


BTW, I had success with another Dell PC with Win 10 Pro about 4 months 
ago.  This problem appears to be a one-off.


Good Install:
/Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the SHOP-PC\Dale account can read the 
path C:\Program Files\PostgreSQL\9.6\data//

//    Executing batch file 'rad0510A.bat'...//
//    processed file: C:\Program Files\PostgreSQL\9.6\data//
//Successfully processed 1 files; Failed processing 0 files/

Bad Install:
/Called AclCheck(D:\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the WINDOWS-6BEGVO1\don king account can 
read the path D:\PostgreSQL\9.6\data//

//    Executing batch file 'rad6DBC7.bat'...//
/

Re: Using the public schema

[Thomas Poty]

Erratum :

The main problem with the public schéma is the default privileges (*create*)
granted to all users able to connect.


Le lun. 23 avr. 2018 à 21:16, Thomas Poty  a écrit :

> Hi charlin,
> I invite you to  read this doc it explains very well  the security issue
> with the public schéma :
> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
>
> The main problem with the public schéma is the default privileges
> (execute) granted to all users able to connect.
>
> Personally, each time i create a database, i remove the public schema
>
> Regards
> Thomas
>
> Le lun. 23 avr. 2018 à 17:00, Charlin Barak  a
> écrit :
>
>> Hi,
>> We will be developing three new applications in PostgreSQL, each having
>> its own database instance running on different hosts. We will only have one
>> schema per Postgres instance. The data is read-write only by one
>> application/schema in the DB instance and the data is published to other
>> applications via API calls.
>>
>> In such a standalone database configuration, are there any security
>> implications or any downsides to creating the application in the public
>> schema?
>>
>> Thanks.
>>
>>
>>

rh-postgresql96 vs community postgresql-9.6

[yogeshr]

Our client has provided us Red Hat machine for setting up PostgreSQL. We are
allowed to install red hat provided packages only.

So we have to install red hat provided postgresql (rh-postgresql96) instead
of postgresql-9.6.

Is it a good approach to go with red hat specific PostgreSQL in production
instead of community PostgreSQL? 
Also, we were trying to set up repmgr for this PostgreSQL in the local
environment but we didn't find any suitable package for this PostgreSQL.

How can we install any high availability solution like repmgr for Red Hat
provided PostgreSQL?

any help would be appreciated.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Postgres PAF setup

[Andrew Edenburn]

I am having issues with my PAF setup.  I am new to Postgres and have setup the 
cluster as seen below.
I am getting this error when trying to start my cluster resources.

Master/Slave Set: pgsql-ha [pgsqld]
 pgsqld (ocf::heartbeat:pgsqlms):   FAILED dcmilphlum224 (unmanaged)
 pgsqld (ocf::heartbeat:pgsqlms):   FAILED dcmilphlum223 (unmanaged)
pgsql-master-ip(ocf::heartbeat:IPaddr2):   Started dcmilphlum223

Failed Actions:
* pgsqld_stop_0 on dcmilphlum224 'unknown error' (1): call=239, 
status=complete, exitreason='Unexpected state for instance "pgsqld" (returned 
1)',
last-rc-change='Mon Apr 23 13:11:17 2018', queued=0ms, exec=95ms
* pgsqld_stop_0 on dcmilphlum223 'unknown error' (1): call=248, 
status=complete, exitreason='Unexpected state for instance "pgsqld" (returned 
1)',
last-rc-change='Mon Apr 23 13:11:17 2018', queued=0ms, exec=89ms

cleanup and clear is not fixing any issues and I am not seeing anything in the 
logs.  Any help would be greatly appreciated.


My cluster config
root@dcmilphlum223:/usr/lib/ocf/resource.d/heartbeat# crm config
crm(live)configure# show
node 1: dcmilphlum223
node 2: dcmilphlum224 \
attributes pgsqld-data-status=LATEST
primitive pgsql-master-ip IPaddr2 \
params ip=10.125.75.188 cidr_netmask=23 nic=bond0.283 \
op monitor interval=10s \
meta target-role=Started
primitive pgsqld pgsqlms \
params pgdata="/pgsql/data/pg7000" bindir="/usr/local/pgsql/bin" 
pgport=7000 start_opts="-c config_file=/pgsql/data/pg7000/postgresql.conf" 
recovery_template="/pgsql/data/pg7000/recovery.conf.pcmk" \
op start interval=0 timeout=60s \
op stop interval=0 timeout=60s \
op promote interval=0 timeout=30s \
op demote interval=0 timeout=120s \
op monitor enabled=true interval=15s role=Master timeout=10s \
op monitor enabled=true interval=16s role=Slave timeout=10s \
op notify interval=0 timeout=60s \
meta
ms pgsql-ha pgsqld \
meta notify=true target-role=Stopped
property cib-bootstrap-options: \
have-watchdog=false \
dc-version=1.1.14-70404b0 \
cluster-infrastructure=corosync \
cluster-name=pgsql_cluster \
stonith-enabled=false \
no-quorum-policy=ignore \
migration-threshold=1 \
last-lrm-refresh=1524503476
rsc_defaults rsc_defaults-options: \
migration-threshold=5 \
resource-stickiness=10
crm(live)configure#


My pcs Config
Corosync Nodes:
dcmilphlum223 dcmilphlum224
Pacemaker Nodes:
dcmilphlum223 dcmilphlum224

Resources:
Master: pgsql-ha
  Meta Attrs: notify=true target-role=Stopped
  Resource: pgsqld (class=ocf provider=heartbeat type=pgsqlms)
   Attributes: pgdata=/pgsql/data/pg7000 bindir=/usr/local/pgsql/bin 
pgport=7000 start_opts="-c config_file=/pgsql/data/pg7000/postgresql.conf" 
recovery_template=/pgsql/data/pg7000/recovery.conf.pcmk
   Operations: start interval=0 timeout=60s (pgsqld-start-0)
   stop interval=0 timeout=60s (pgsqld-stop-0)
   promote interval=0 timeout=30s (pgsqld-promote-0)
   demote interval=0 timeout=120s (pgsqld-demote-0)
   monitor role=Master timeout=10s interval=15s enabled=true 
(pgsqld-monitor-interval-15s)
   monitor role=Slave timeout=10s interval=16s enabled=true 
(pgsqld-monitor-interval-16s)
   notify interval=0 timeout=60s (pgsqld-notify-0)
Resource: pgsql-master-ip (class=ocf provider=heartbeat type=IPaddr2)
  Attributes: ip=10.125.75.188 cidr_netmask=23 nic=bond0.283
  Meta Attrs: target-role=Started
  Operations: monitor interval=10s (pgsql-master-ip-monitor-10s)

Stonith Devices:
Fencing Levels:

Location Constraints:
Ordering Constraints:
Colocation Constraints:

Resources Defaults:
migration-threshold: 5
resource-stickiness: 10
Operations Defaults:
No defaults set

Cluster Properties:
cluster-infrastructure: corosync
cluster-name: pgsql_cluster
dc-version: 1.1.14-70404b0
have-watchdog: false
last-lrm-refresh: 1524503476
migration-threshold: 1
no-quorum-policy: ignore
stonith-enabled: false
Node Attributes:
dcmilphlum224: pgsqld-data-status=LATEST


[cid:0__=0ABBF137DFA7B0688f9e8a93df93869091@local]
Andrew A Edenburn
General Motors
Hyperscale Computing & Core Engineering
Mobile Phone: +01-810-410-6008
30009 Van Dyke Ave
Warren, MI. 
48090-9026
Cube: 2w05-21
mailto:andrew.edenb...@gm.com
Web Connect SoftPhone 586-986-4864
[cid:image002.jpg@01D3DB0C.B9AEFDA0]



Nothing in this message is intended to constitute an electronic signature 
unless a specific statement to the contrary is included in this message.

Confidentiality Note: This message is intended only for the person or entity to 
which it is addressed. It may contain confidential and/or privileged material. 
Any review, transmission, dissemination or other use, or taking of any 

Re: Strange error in Windows 10 Pro

[Adrian Klaver]

On 04/23/2018 07:09 PM, Dale Seaburg wrote:
Thanks to Moreno and Igor for the Event Viewer suggestions.  Here are a 
few lines of log file where they differ between a good install and a bad 
incomplete install.  The good install was from my Shop PC with Windows 
10 Pro.


BTW, I had success with another Dell PC with Win 10 Pro about 4 months 
ago.  This problem appears to be a one-off.


So is the below from the bitrock_installer logs or the Event Viewer logs?

Big difference I notice is C:\ vs D:\.
Where is D:\?



Good Install:
/Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the SHOP-PC\Dale account can read the path 
C:\Program Files\PostgreSQL\9.6\data//

//    Executing batch file 'rad0510A.bat'...//
//    processed file: C:\Program Files\PostgreSQL\9.6\data//
//Successfully processed 1 files; Failed processing 0 files/

Bad Install:
/Called AclCheck(D:\PostgreSQL\9.6\data)//
//Called IsVistaOrNewer()...//
//    'winmgmts' object initialized...//
//    Version:10.//
//    MajorVersion:10//
//Executing icacls to ensure the WINDOWS-6BEGVO1\don king account can 
read the path D:\PostgreSQL\9.6\data//

//    Executing batch file 'rad6DBC7.bat'...//
//
/Notice the last four lines of the *Good Install* vs the last two lines 
of the *Bad Install*.  There is no indication of processing, or whether 
it was successful or not in the bad install.


As you can see, I even took Igor's suggestion to install in a non-system 
(Program Files) path, to no success.


Again, not sure what to do.  Open for suggestions...  I'm almost ready 
to call Dell and complain about a "bad" Win 10 Pro install.


Dale



--
Adrian Klaver
adrian.kla...@aklaver.com