Re: [GENERAL] Materialized view auto refresh

[Nguyễn Trần Quốc Vinh]

Dear Harry.

You can try this while you are waiting the better solution from Prof. Kevin
Grittner. We did not improve because we do not know if we are in the right
way...

https://www.postgresql.org/message-id/caau9oxsb5gy8lz12kqaa3r1iv19c7famnefixdac1fhrfyt...@mail.gmail.com

You can downloat the source and the binary from here
http://it.ued.udn.vn/myprojects/pgTriggerGen/.

Best regards,

TS. Nguyễn Trần Quốc Vinh
---
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn ; http://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh 
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98

Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn ;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh 
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Tue, Aug 9, 2016 at 8:49 PM, Kevin Grittner  wrote:

> On Tue, Aug 9, 2016 at 4:50 AM, hari.prasath 
> wrote:
>
> >   Is there any tentative schedule for real-time or incremental(only
> > applying delta changes) refresh of materialized views.?.
>
> There is work in progress, but no hard schedule.  Unfortunately, it
> has often been set aside to address more immediate issues for
> particular end users; but I expect to get back to it Real Soon Now.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[amulsul]

Try using double colon opperator instead of cast().  
E. g.  task_start::date

Regards, 
Amul

Sent from a mobile device. Please excuse brevity and tpyos. 
 
  On Mon, 5 Sep, 2016 at 8:29 am, Patrick Baker [via 
PostgreSQL] wrote:   Hi guys,
I got the tasks table that has the tasks_start column:
tasks_start        | timestamp(3) without time zone 


select tasks_start from tasks LIMIT 1;
tasks_start           
--- 
2016-08-10 00:30:00 

 I'm trying to cast the date, using this query:
SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f' AND 
"tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')

but it doesn't work.. I get 0 rows... what am I doing wrong?
cheersPatrick  
 
   If you reply to this email, your message will be added to the discussion 
below: 
http://postgresql.nabble.com/Get-date-timestamp-3-without-time-zone-column-PGSQL-9-5-tp5919421.html
   To start a new topic under PostgreSQL - general, email 
ml-node+s1045698n1843780...@n3.nabble.com 
 To unsubscribe from PostgreSQL - general, click here.
 NAML   




--
View this message in context: 
http://postgresql.nabble.com/Get-date-timestamp-3-without-time-zone-column-PGSQL-9-5-tp5919421p5919430.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Lucas Possamai]

2016-09-05 15:17 GMT+12:00 Patrick B :

>
>> You might want to share the version of PostgreSQL you are using.
>>
>> You might want to try date_trunc and AT TIMEZONE function/operators-
>>
>> https://www.postgresql.org/docs/9.4/static/functions-datetim
>> e.html#FUNCTIONS-DATETIME-TRUNC
>>
>> SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')
>>
>> Note: I have not tried this statement
>>
>> Is this something you are going to use often? If that is the case then
>> consider to re-model your query. The moment you use an expression on a
>> column it would not use a normal BTree index.
>>
>>
> Hmm... I see
>
>
> select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks
>
>
>  And I get:
>
> 2016-08-10 00:00:00
>
>
>
> I actually need just the date 2016-08-10, without 00:00:00...
> Any idea?
>
> Cheers
> Patrick
>


I agree with @Tom, your first sql should work... Based on the table name
"tasks" you provided, try this:


SELECT cast(jtasks_start as date) FROM "tasks" WHERE (date(tasks_start) in
> ('2016-08-11'))



Lucas

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Pavel Stehule]

2016-09-05 5:17 GMT+02:00 Patrick B :

>
>> You might want to share the version of PostgreSQL you are using.
>>
>> You might want to try date_trunc and AT TIMEZONE function/operators-
>>
>> https://www.postgresql.org/docs/9.4/static/functions-datetim
>> e.html#FUNCTIONS-DATETIME-TRUNC
>>
>> SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')
>>
>> Note: I have not tried this statement
>>
>> Is this something you are going to use often? If that is the case then
>> consider to re-model your query. The moment you use an expression on a
>> column it would not use a normal BTree index.
>>
>>
> Hmm... I see
>
>
> select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks
>
>
>  And I get:
>
> 2016-08-10 00:00:00
>
>
>
> I actually need just the date 2016-08-10, without 00:00:00...
>

cast to date.

PostgreSQL timestamp shows time always.

Regards

Pavel


> Any idea?
>
> Cheers
> Patrick
>

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Patrick B]

>
>
> You might want to share the version of PostgreSQL you are using.
>
> You might want to try date_trunc and AT TIMEZONE function/operators-
>
> https://www.postgresql.org/docs/9.4/static/functions-
> datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')
>
> Note: I have not tried this statement
>
> Is this something you are going to use often? If that is the case then
> consider to re-model your query. The moment you use an expression on a
> column it would not use a normal BTree index.
>
>
Hmm... I see


select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks


 And I get:

2016-08-10 00:00:00



I actually need just the date 2016-08-10, without 00:00:00...
Any idea?

Cheers
Patrick

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Tom Lane]

Patrick B  writes:
>  I'm trying to cast the date, using this query:
>> SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f'
>> AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')
> but it doesn't work.. I get 0 rows... what am I doing wrong?

Are you sure you're not getting an error?  The query is specifying fields
in "tasks" but the FROM clause only lists "jobs".

Either one of those two cast-to-date syntaxes should work, so your problem
is somewhere else.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Sameer Kumar]

On Mon, Sep 5, 2016 at 10:59 AM Patrick B  wrote:

> Hi guys,
>
> I got the tasks table that has the tasks_start column:
>
>> tasks_start| timestamp(3) without time zone
>
>
>
> select tasks_start from tasks LIMIT 1;
>
>> tasks_start
>> ---
>> 2016-08-10 00:30:00
>
>
>  I'm trying to cast the date, using this query:
>
>> SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" =
>> 'f' AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')
>
>
You might want to share the version of PostgreSQL you are using.

You might want to try date_trunc and AT TIMEZONE function/operators-

https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')

Note: I have not tried this statement

Is this something you are going to use often? If that is the case then
consider to re-model your query. The moment you use an expression on a
column it would not use a normal BTree index.


> but it doesn't work.. I get 0 rows... what am I doing wrong?
>
> cheers
> Patrick
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350

Skype: sameer.ashnik | www.ashnik.com

[GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Patrick B]

Hi guys,

I got the tasks table that has the tasks_start column:

> tasks_start| timestamp(3) without time zone



select tasks_start from tasks LIMIT 1;

> tasks_start
> ---
> 2016-08-10 00:30:00


 I'm trying to cast the date, using this query:

> SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f'
> AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')


but it doesn't work.. I get 0 rows... what am I doing wrong?

cheers
Patrick

Re: [GENERAL] BDR: Transactions with global lock

[Craig Ringer]

On 31 August 2016 at 22:38, Salvatore Tomaselli
 wrote:
> Hello,
>
> I have been looking around in the documentation and I didn't find anything, 
> so I wonder if there is support in bdr for having transactions that happen 
> while the global lock is acquired and get replicated everywhere before the 
> transaction ends.
>
> Is there a way to achieve this?

The purpose of the global DDL lock to to prevent that.

If you ALTER a table on one node, say by ALTERing the type of a
column, while you continue to do write transactions to that table on
other nodes, when the other nodes' data replicates to the node that
does the DROP it won't make sense anymore. What value should be in the
new column?

BDR 1.0 adds a weaker DDL lock mode that is used for types of DDL
where that is not a concern. It only blocks other concurrent DDL. But
for DDL that alters tables it's necessary to block concurrent DML.

BTW, now that it's clear in-core logical replication is going in
another direction there's now a bdr-l...@2ndquadrant.com mailing list;
see https://groups.google.com/a/2ndquadrant.com/forum/#!forum/bdr-list
.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

[Patrick B]

Hi guys,


You can actually reduce the time more by pre-syncing to the new location.

something like:

rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

service postgres stop

rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

The second rsync will only copy the deltas from the first, it still has to
> go in and determine what needs to be copied/what changed but the bulk of it
> can be prepared/migrated before the actual downtime window.



Thanks a lot @David.

I tested the above and it worked... Here is what I did:

1 - Create /var/lib/pgsql2 directory
mkdir /var/lib/pgsql2

2 - Set permissions:
chown -R postgres:postgres /var/lib/pgsql2

3 - Change postgresql.conf:
data_directory = '/var/lib/pgsql2/9.2/data/'

4 - RSYNC:
rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

5 - Stop postgres
/etc/init.d/postgresql-9.2 stop

5 - Re-run RSYNC to incremental copy:
rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

6 - Change /etc/init.d/postgresql-9.2:

*OLD:*
PGDATA=/var/lib/pgsql/9.2/data
PGLOG=/var/lib/pgsql/9.2/pgstartup.log
PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade.log

*NEW:*
PGDATA=/var/lib/pgsql2/9.2/data
PGLOG=/var/lib/pgsql2/9.2/pgstartup.log
PGUPLOG=/var/lib/pgsql2/$PGMAJORVERSION/pgupgrade.log

7 - Start postgres
/etc/init.d/postgresql-9.2 start


and it worked perfectly fine... :)

That's great news! My only concern is about the "*RSYNC*" - Hope that
doesn't take long!!!

This all steps must be performed by me on the next few days/weeks - I'll
keep you guys updated... Keen to see the new DB running in a SSD
environment :)

Re: [GENERAL] please remove outdated site from 2005

[Raymond O'Donnell]

On 04/09/16 19:33, Lukas Lehner wrote:

Hi

when I use a search engine for the term "Postgres Certification"

this page shows up

https://www.postgresql.org/about/news/333/

please remove that, very much outdated. The referenced link doesn't
exist anymore (404 No such domain)


In all fairness, the page on which you find that item is an archive of 
all past news items going back to October 2002. I'm sure there are a lot 
of out-of-date items on it, but there's no real reason to remove them; 
an archive is just that.


Ray.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 12:55 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Another thing that came to mind is compatibility with existing
applications/clients. You say you have been running using trust and I am
betting your client connection parameters reflect that. Now for
connections methods that can 'see' the .pgpass file and use libpq as the
their underlying Postgres library then things should work. Otherwise your
applications may not be able to connect until you supply the correct
password in some manner.


  That's a concern. My business financial software uses postgres as the
backend and a browser UI and I enter my username and password on the login
page. It works with auth method trust. I've no idea exactly how it connects
to the database. Since it ain't broke I won't futz with it and possibly


It is using its own authentication method and tables, independent of 
Postgres, that restrict access to its own data, not the cluster as whole.



break it. I've other things with higher priorities.

Rich






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Another thing that came to mind is compatibility with existing
applications/clients. You say you have been running using trust and I am
betting your client connection parameters reflect that. Now for
connections methods that can 'see' the .pgpass file and use libpq as the
their underlying Postgres library then things should work. Otherwise your
applications may not be able to connect until you supply the correct
password in some manner.


  That's a concern. My business financial software uses postgres as the
backend and a browser UI and I enter my username and password on the login
page. It works with auth method trust. I've no idea exactly how it connects
to the database. Since it ain't broke I won't futz with it and possibly
break it. I've other things with higher priorities.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 12:42 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Well first, if you are going to use trust as your auth method then
specifying a password is moot exercise.


  I tried adding an explicit password to ~/.pgpass with md5 as the auth
method, but that didn't work so I went back to trust. That's served well
for
19 years. :-)


auth is not something you enter in .pgpass. The format for the file is 
spelled out here:


https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html





Second, not sure where you are in the process, but any time you change
the
pg_hba.conf file you will need to give Postgres a reload signal to get it
to recognize the changes. Again not sure how you are signalling Postgres
but if you are using pg_ctl
https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html
then something like:

pg_ctl reload  -D path_to_your_datadir

as OS user postgres.


  Good to know. I use pg_ctl stop and start with the path on both command
lines.


Third, .pgpass should hold information that already exists in the
database
system tables. It is not a mechanism for entering that information into
the database. So yes, you will need to use ALTER ROLE to create the
password inside Postgres.


  OK. I'll try that for the learning experience.

  So much to learn and so little time ...

Thanks,

Rich





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 12:10 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


You don't it directly. That information is supplied by Postgres when you
do CREATE or ALTER ROLE. The -W switch just does that for the
superuser(postgres in your case) when you initdb a new cluster.


Adrian,

  OK. That makes sense.


Sorry, old habits. pg_user is a version of the pg_shadow view that blanks
out the actual password. pg_shadow is a view over the table pg_authid,
where the actual information is stored now. In any case, again they are
not tables/views you directly modify.


  Good. Then I won't spend time with them.

  So, given my single-user situation do you think that I should ALTER ROLE
to add my password? Adding it to ~/.pgpass did nothing positive when I
changed auth method to md5; my attempt to open a database failed because
that password was rejected. Strange ... to me.


Another thing that came to mind is compatibility with existing 
applications/clients. You say you have been running using trust and I am 
betting your client connection parameters reflect that. Now for 
connections methods that can 'see' the .pgpass file and use libpq as the 
their underlying Postgres library then things should work. Otherwise 
your applications may not be able to connect until you supply the 
correct password in some manner.




Rich






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:

Well first, if you are going to use trust as your auth method then specifying 
a password is moot exercise.


  I tried adding an explicit password to ~/.pgpass with md5 as the auth
method, but that didn't work so I went back to trust. That's served well for
19 years. :-)


Second, not sure where you are in the process, but any time you change the
pg_hba.conf file you will need to give Postgres a reload signal to get it
to recognize the changes. Again not sure how you are signalling Postgres
but if you are using pg_ctl
https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html
then something like:

pg_ctl reload  -D path_to_your_datadir

as OS user postgres.


  Good to know. I use pg_ctl stop and start with the path on both command
lines.


Third, .pgpass should hold information that already exists in the database
system tables. It is not a mechanism for entering that information into
the database. So yes, you will need to use ALTER ROLE to create the
password inside Postgres.


  OK. I'll try that for the learning experience.

  So much to learn and so little time ...

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 12:10 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


You don't it directly. That information is supplied by Postgres when you
do CREATE or ALTER ROLE. The -W switch just does that for the
superuser(postgres in your case) when you initdb a new cluster.


Adrian,

  OK. That makes sense.


Sorry, old habits. pg_user is a version of the pg_shadow view that blanks
out the actual password. pg_shadow is a view over the table pg_authid,
where the actual information is stored now. In any case, again they are
not tables/views you directly modify.


  Good. Then I won't spend time with them.

  So, given my single-user situation do you think that I should ALTER ROLE
to add my password? Adding it to ~/.pgpass did nothing positive when I
changed auth method to md5; my attempt to open a database failed because
that password was rejected. Strange ... to me.


Well first, if you are going to use trust as your auth method then 
specifying a password is moot exercise.


Second, not sure where you are in the process, but any time you change 
the pg_hba.conf file you will need to give Postgres a reload signal to 
get it to recognize the changes.  Again not sure how you are signalling 
Postgres but if you are using pg_ctl


https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html

then something like:

pg_ctl reload  -D path_to_your_datadir

as OS user postgres.


Third, .pgpass should hold information that already exists in the 
database system tables. It is not a mechanism for entering that 
information into the database. So yes, you will need to use ALTER ROLE 
to create the password inside Postgres.




Rich






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


You don't it directly. That information is supplied by Postgres when you
do CREATE or ALTER ROLE. The -W switch just does that for the
superuser(postgres in your case) when you initdb a new cluster.


Adrian,

  OK. That makes sense.


Sorry, old habits. pg_user is a version of the pg_shadow view that blanks
out the actual password. pg_shadow is a view over the table pg_authid,
where the actual information is stored now. In any case, again they are
not tables/views you directly modify.


  Good. Then I won't spend time with them.

  So, given my single-user situation do you think that I should ALTER ROLE
to add my password? Adding it to ~/.pgpass did nothing positive when I
changed auth method to md5; my attempt to open a database failed because
that password was rejected. Strange ... to me.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 09:43 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


-W is not referring to the OS user but the database superuser. Now in
your
case they have the same name, postgres. The settings in /etc/passwd are
not relevant to what -W is doing. -W is referring to user information
being stored in the cluster in the system tables pg_user and pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html


  I see the pg_user in Section 49.79 but don't know how to correctly
configure it. It seems that pg_shadow is unnecessary since I don't need to
provide compatibility with versions < 8.1.


You don't it directly. That information is supplied by Postgres when you 
do CREATE or ALTER ROLE. The -W switch just does that for the 
superuser(postgres in your case) when you initdb a new cluster.


Sorry, old habits. pg_user is a version of the pg_shadow view that 
blanks out the actual password. pg_shadow is a view over the table 
pg_authid, where the actual information is stored now. In any case, 
again they are not tables/views you directly modify.




Rich





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] please remove outdated site from 2005

[Lukas Lehner]

Hi

when I use a search engine for the term "Postgres Certification"

this page shows up

https://www.postgresql.org/about/news/333/

please remove that, very much outdated. The referenced link doesn't exist
anymore (404 No such domain)

Lukas

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


I would take Charles's suggestion and set up a .pgpass file just to be
safe.


  The file ~/.pgpass already exists, but without an explicit password. I
added my password (plain text). The file already had perms 0600. Perhaps my
password was rejected with the authorization method set to md5 was because
the password field was missing.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


-W is not referring to the OS user but the database superuser. Now in your
case they have the same name, postgres. The settings in /etc/passwd are
not relevant to what -W is doing. -W is referring to user information
being stored in the cluster in the system tables pg_user and pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html


  I see the pg_user in Section 49.79 but don't know how to correctly
configure it. It seems that pg_shadow is unnecessary since I don't need to
provide compatibility with versions < 8.1.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Just be aware that you now have a password for the postgres user and that
if you ever do enable md5 you will need it. I would take Charles's
suggestion and set up a .pgpass file just to be safe.


Adrian,

 OK. I'll also read the page at the URL you provided and add a password for
me, too.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Adrian Klaver]

On 09/04/2016 09:01 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually there is an important difference. In your 9.3 file you have set
METHOD set to trust and in the 9.5 file it is set to md5, which is
password. Set the METHOD to trust in your 9.5 file and restart the
database. Now for the non-socket access methods this is a security risk,
so you will want to change it back at some point once you get the
users/passwords figured out


Adrian,

  I saw that but missed the meaning. I'm the only user on this system so
there is no internal security risk.


Forgot an option in my previous reply, removing the password:

https://www.postgresql.org/docs/9.5/static/sql-alterrole.html

ALTER ROLE postgres WITH PASSWORD NULL;



  After learning that the Slackware script's restart does not work[1] I
stopped the process then restarted it. I can now access my databases and
the
financial software via the browser UI.

  Now I can remove the 9.3.4 directory and be comfortable that the next
upgrade will be smooth.

  Thanks very much for your patient help!

Much appreciated,

Rich

[1] I've seen this same behavior in other rc. scripts. Stopping
and starting works in all cases.





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Adrian Klaver]

On 09/04/2016 09:01 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually there is an important difference. In your 9.3 file you have set
METHOD set to trust and in the 9.5 file it is set to md5, which is
password. Set the METHOD to trust in your 9.5 file and restart the
database. Now for the non-socket access methods this is a security risk,
so you will want to change it back at some point once you get the
users/passwords figured out


Adrian,

  I saw that but missed the meaning. I'm the only user on this system so
there is no internal security risk.


Just be aware that you now have a password for the postgres user and 
that if you ever do enable md5 you will need it. I would take Charles's 
suggestion and set up a .pgpass file just to be safe.




  After learning that the Slackware script's restart does not work[1] I
stopped the process then restarted it. I can now access my databases and
the
financial software via the browser UI.

  Now I can remove the 9.3.4 directory and be comfortable that the next
upgrade will be smooth.

  Thanks very much for your patient help!

Much appreciated,

Rich

[1] I've seen this same behavior in other rc. scripts. Stopping
and starting works in all cases.





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


-W is not referring to the OS user but the database superuser. Now in your
case they have the same name, postgres. The settings in /etc/passwd are
not relevant to what -W is doing. -W is referring to user information
being stored in the cluster in the system tables pg_user and pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html


Adrian,

  I did not know this. Thanks for the information.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Well, there you have it. As Adrian suggested you may set temporarily the
authentication method to trust, set yourself a password and change it back
to md5.


Charles,

  I've had a password on this LAN for almost 2 decades. I've not before set
a password specifically for postgresql. Since I'm the only user and the
trust authentification has worked for the same period, I'll leve it that
way.

Thanks for your help,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually there is an important difference. In your 9.3 file you have set
METHOD set to trust and in the 9.5 file it is set to md5, which is
password. Set the METHOD to trust in your 9.5 file and restart the
database. Now for the non-socket access methods this is a security risk,
so you will want to change it back at some point once you get the
users/passwords figured out


Adrian,

  I saw that but missed the meaning. I'm the only user on this system so
there is no internal security risk.

  After learning that the Slackware script's restart does not work[1] I
stopped the process then restarted it. I can now access my databases and the
financial software via the browser UI.

  Now I can remove the 9.3.4 directory and be comfortable that the next
upgrade will be smooth.

  Thanks very much for your patient help!

Much appreciated,

Rich

[1] I've seen this same behavior in other rc. scripts. Stopping
and starting works in all cases.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 08:14 AM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: FATAL:  password authentication failed for
user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: psql (9.5.4)
Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.


Asked and answered. If you want the complete information:

https://www.postgresql.org/docs/9.5/static/client-authentication.html



Thanks again,

Rich





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 08:11 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


But the message you sent me offlist showed the 9.5 instance running.


  But now it's not running.


How are you starting the instance?


  As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &

  After removing an orphaned postmaster.pid the above seemed to have
started
postgres, but there's no postmaster process running.


Are you sure that the password being asked for is not for the OS user
you are using to run whatever start script you are using?


  Thinking postmaster is running I tried this:

$ psql crm
Password: FATAL:  password authentication failed for user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

  The crm database is owned by me.


I have never used it, but I am pretty sure that is not what -W means. It
looks to me that it asks you to create a password at init for the
database
superuser(in this case postgres) and only that user when that user tries
to use log into a database after the cluster is started.


  From man initdb:

-W, --pwprompt
   Makes initdb prompt for a password to give the database
superuser.
   If you don't plan on using password authentication, this is not
   important. Otherwise you won't be able to use password
   authentication until you have a password set up.

  The superuser already exists in /etc/passwd.


-W is not referring to the OS user but the database superuser. Now in 
your case they have the same name, postgres. The settings in /etc/passwd 
are not relevant to what -W is doing. -W is referring to user 
information being stored in the cluster in the system tables pg_user and 
pg_shadow:


https://www.postgresql.org/docs/9.5/static/catalogs.html




Do you remember what password you specified?


  Yes. It's the same password I use for logging in as a user. If it's the
superuser password being requested, then that's the same as my user
password.

  The Slackware rc.postgresql file for 9.5 has changed from 9.3 and
earlier.
It's asking for passwords:

 if [ ! -e $DATADIR/PG_VERSION ]; then
echo "You should initialize the PostgreSQL database
at location $DATADIR"
echo "e.g. su postgres -c \"initdb -D $DATADIR
--locale=en_US.UTF-8 -A md5 -W\""
exit 6
 fi

  Note the '-W' at the end. But, I ran initdb from the command line as user
postgres.


The -W is not the issue the auth MEHOD set in your pg_hba.conf is. Set 
it to trust for now. I'm guessing that since you ran using trust in your 
9.3 instance you do not actually have any passwords set up for the 
users. This means there are also none in the new 9.5 cluster created 
from the dump file, with one exception. That is since you used -W to 
initdb you now have a password for the postgres user.




Rich







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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hi

On 09/04/2016 05:16 PM, Charles Clavadetscher wrote:

Hi

On 09/04/2016 05:14 PM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: FATAL:  password authentication failed for
user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: psql (9.5.4)
Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.


Adrian already answered that. You can set the connection authentication
method to trust. This can have some risks, however.


You may also consider using a .pgpass file:

https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html

Bye
Charles



Bye
Charles



Thanks again,

Rich






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hi

On 09/04/2016 05:14 PM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: FATAL:  password authentication failed for
user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: psql (9.5.4)
Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.


Adrian already answered that. You can set the connection authentication 
method to trust. This can have some risks, however.


Bye
Charles



Thanks again,

Rich




--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hello

On 09/04/2016 05:11 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


But the message you sent me offlist showed the 9.5 instance running.


  But now it's not running.


How are you starting the instance?


  As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &

  After removing an orphaned postmaster.pid the above seemed to have
started
postgres, but there's no postmaster process running.


Are you sure that the password being asked for is not for the OS user
you are using to run whatever start script you are using?


  Thinking postmaster is running I tried this:

$ psql crm
Password: FATAL:  password authentication failed for user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"


Well, there you have it. As Adrian suggested you may set temporarily the 
authentication method to trust, set yourself a password and change it 
back to md5.


That should do.
Bye
Charles



  The crm database is owned by me.


I have never used it, but I am pretty sure that is not what -W means. It
looks to me that it asks you to create a password at init for the
database
superuser(in this case postgres) and only that user when that user tries
to use log into a database after the cluster is started.


  From man initdb:

-W, --pwprompt
   Makes initdb prompt for a password to give the database
superuser.
   If you don't plan on using password authentication, this is not
   important. Otherwise you won't be able to use password
   authentication until you have a password set up.

  The superuser already exists in /etc/passwd.


Do you remember what password you specified?


  Yes. It's the same password I use for logging in as a user. If it's the
superuser password being requested, then that's the same as my user
password.

  The Slackware rc.postgresql file for 9.5 has changed from 9.3 and
earlier.
It's asking for passwords:

 if [ ! -e $DATADIR/PG_VERSION ]; then
echo "You should initialize the PostgreSQL database
at location $DATADIR"
echo "e.g. su postgres -c \"initdb -D $DATADIR
--locale=en_US.UTF-8 -A md5 -W\""
exit 6
 fi

  Note the '-W' at the end. But, I ran initdb from the command line as user
postgres.

Rich






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sat, 3 Sep 2016, Adrian Klaver wrote:

Or if you are fine running the 9.5 instance at port 5432, what happens if you 
do?:


psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: 
FATAL:  password authentication failed for user "rshepard"

DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: 
psql (9.5.4)

Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.

Thanks again,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


But the message you sent me offlist showed the 9.5 instance running.


  But now it's not running.


How are you starting the instance?


  As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &

  After removing an orphaned postmaster.pid the above seemed to have started
postgres, but there's no postmaster process running.

Are you sure that the password being asked for is not for the OS user you are 
using to run whatever start script you are using?


  Thinking postmaster is running I tried this:

$ psql crm
Password: 
FATAL:  password authentication failed for user "rshepard"

DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

  The crm database is owned by me.


I have never used it, but I am pretty sure that is not what -W means. It
looks to me that it asks you to create a password at init for the database
superuser(in this case postgres) and only that user when that user tries
to use log into a database after the cluster is started.


  From man initdb:

-W, --pwprompt
   Makes initdb prompt for a password to give the database superuser.
   If you don't plan on using password authentication, this is not
   important. Otherwise you won't be able to use password
   authentication until you have a password set up.

  The superuser already exists in /etc/passwd.


Do you remember what password you specified?


  Yes. It's the same password I use for logging in as a user. If it's the
superuser password being requested, then that's the same as my user
password.

  The Slackware rc.postgresql file for 9.5 has changed from 9.3 and earlier.
It's asking for passwords:

 if [ ! -e $DATADIR/PG_VERSION ]; then
echo "You should initialize the PostgreSQL database
at location $DATADIR"
echo "e.g. su postgres -c \"initdb -D $DATADIR
--locale=en_US.UTF-8 -A md5 -W\""
exit 6
 fi

  Note the '-W' at the end. But, I ran initdb from the command line as user
postgres.

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 07:07 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


The above is not possible. If the postmaster was not running there
would be no rejection error.


Adrian,

  Yes. I conflated when the postmaster was running -- using the 9.3.4
server
-- and now when it's not running. Now I'm wondering if I mistakenly used
the
-W option to initdb which requires password use for access.


Don't do that, you will end up right back at this point again. Find
the pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and
post them here.


  OK. But when I used 'diff -y' I saw only a paragraph of comments as
different. I'll try attaching them; they're ~100 lines each. If the
attachments are stripped off my the mlm I'll list them within the message
body.


Actually there is an important difference. In your 9.3 file you have set 
METHOD set to trust and in the 9.5 file it is set to md5, which is 
password. Set the METHOD to trust in your 9.5 file and restart the 
database. Now for the non-socket access methods this is a security risk, 
so you will want to change it back at some point once you get  the 
users/passwords figured out


Rich








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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 07:08 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Are you able to connect to the new instance with any user at all, e.g.
with psql? If so you can use the command \du to list all users.


Charles,

  No. The postmaster is not running; trying to start it requires a password
which is also rejected.


But the message you sent me offlist showed the 9.5 instance running.

How are you starting the instance?
Are you sure that the password being asked for is not for the OS user 
you are using to run whatever start script you are using?




  Since I have the pg_dumpall data in a large .sql file I think the best
solution is for me to remove the 9.5.4 package completely, re-install from
the package tarball, run initdb, then start the postmaster and restore all
the databases in the cluster.

  I'm thinking that without paying attention when I initially ran initdb I
used a commandline found on a web site (rather than just reading the man
page) and used the -W option which requires a password for everything. So
starting over from scratch _should_ remove all issues and successfully end
this thread.


I have never used it, but I am pretty sure that is not what -W means. It 
looks to me that it asks you to create a password at init for the 
database superuser(in this case postgres) and only that user when that 
user tries  to use log into a database after the cluster is started.


Do you remember what password you specified?


Thanks,

Rich






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgres_fdw + presto

[Nicolas Paris]

Hello,

Has anyone already tested to integrate presto (https://prestodb.io/) within
postgresql thought the postgres_fdw extension ?

Presto is a distributed SQL query engine able to scale horizontally on top
of hadoop, cassandra or mongodb.
Moreover, presto has a PostgreSQL protocol (
https://github.com/treasure-data/prestogres).

Since postgres_fdw allows predicate/join(coming in pg 9.6) push down, this
gateway sounds good to me, and I wonder if anyone started to look in that
direction ?

Thanks

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually you already have. From an email that I just realized was offlist:

"As superuser postgres connected to 9.5.4 'psql -l' shows all databases
loaded:

xxx  | rshepard   | UTF8 | C   | en_US.UTF-8 |
xxx| rshepard   | UTF8 | C   | en_US.UTF-8 | rshepard=CT


  I did not recognize this as the answer to Charles' question.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Are you able to connect to the new instance with any user at all, e.g.
with psql? If so you can use the command \du to list all users.


Charles,

  No. The postmaster is not running; trying to start it requires a password
which is also rejected.

  Since I have the pg_dumpall data in a large .sql file I think the best
solution is for me to remove the 9.5.4 package completely, re-install from
the package tarball, run initdb, then start the postmaster and restore all
the databases in the cluster.

  I'm thinking that without paying attention when I initially ran initdb I
used a commandline found on a web site (rather than just reading the man
page) and used the -W option which requires a password for everything. So
starting over from scratch _should_ remove all issues and successfully end
this thread.

Thanks,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 05:40 AM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Does the user rshepard exist in the new 9.5 instance?


  I assume so but do not know how to check this.


Actually you already have. From an email that I just realized was offlist:

"As superuser postgres connected to 9.5.4 'psql -l' shows all databases
loaded:

xxx  | rshepard   | UTF8 | C   | en_US.UTF-8 |
xxx| rshepard   | UTF8 | C   | en_US.UTF-8 | rshepard=CT
"


Rich





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:

The above is not possible. If the postmaster was not running there would be 
no rejection error.


Adrian,

  Yes. I conflated when the postmaster was running -- using the 9.3.4 server
-- and now when it's not running. Now I'm wondering if I mistakenly used the
-W option to initdb which requires password use for access.

Don't do that, you will end up right back at this point again. Find the 
pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and post them 
here.


  OK. But when I used 'diff -y' I saw only a paragraph of comments as
different. I'll try attaching them; they're ~100 lines each. If the
attachments are stripped off my the mlm I'll list them within the message
body.

Rich


# PostgreSQL Client Authentication Configuration File
# ===
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local  DATABASE  USER  METHOD  [OPTIONS]
# host   DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostsslDATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# --
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser.  If you do not trust all your local users,
# use another authentication method.


# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication

Re: [GENERAL] Upgrading using pg_dumpall

[Adrian Klaver]

On 09/04/2016 06:26 AM, Rich Shepard wrote:

Ccing list

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Well the pg_dumpall *.sql file has the global
information(users/passwords), so it is a chicken and egg problem.

FYI, you can do:

pg_dumpall -g
https://www.postgresql.org/docs/9.5/static/app-pg-dumpall.html

to get only those globals and then use that to prime the new instance
with that information.


Adrian,

  The postmaster is not running and when I try to connect to a database I'm
asked for my password which is rejected.


The above is not possible. If the postmaster was not running there would 
be no rejection error.




  I'm thinking what I should do is remove the postgresql-9.5.4 package
(which deletes everything) and re-install it. Since 9.3.4 is not running I
assume that after running initdb I can read the saved pg_dumpall .sql file
and be running 9.5.4 without password requests and rejections.


Don't do that, you will end up right back at this point again. Find the 
pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and post 
them here.




Your thoughts, please,

Rich



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Does the user rshepard exist in the new 9.5 instance?


  I assume so but do not know how to check this.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGDATA / data_directory

[Christoph Berg]

Re: Benoit Lobréau 2016-08-31 

> Hi,
> 
> My company is using PGDATA to store configuration files and the guc
> data_directory to give the path to the instance directory.
> 
> They would use it like this:
> 
> pg_ctl start -D  -w
> 
> with this directory setup:
> 
> /CONFDIR => postgresql.conf pg_hba.conf pg_ident.conf
> /SYSTEM => All the normal stuff in the postgres instance
> directory + recovery.conf recovery.done etc...
> 
> Is it commonly used ?

That's the default cluster layout on Debian (and Debian-based
distributions).

/etc/postgresql/9.5/main/
/var/lib/postgresql/9.5/main/
/var/run/postgresql/9.5-main.pg_stat_tmp/

Christoph


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general