Re: [GENERAL] Top posting....

[Tom Lane]

Alvaro Herrera  writes:
> George Neuner wrote:
>> I agree 100%.  But excessive brevity can make it so a reader can't
>> follow the conversation.  Users of web forums often assume *you* can
>> easily look back up the thread because *they* can.  In my experience,
>> it isn't always easy to do.

> Fortunately, we (postgresql.org) have set up our mailing list archives
> so that it _is_ possible to look back entire threads.  Our archives have
> proven time and again an extremely valuable resource, and we pride on
> their quality and completeness (and the fact that we never ever break
> links even when the website is rewritten).

Amen to the value.  Thanks to those who've made this happen.

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] Top posting....

[Alvaro Herrera]

George Neuner wrote:
> On Thu, 11 May 2017 13:43:52 -0400, Tom Lane 
> wrote:

> >Personally, when I've scrolled down through a couple of pages of quoted
> >and re-quoted text and see no sign of it ending any time soon, I tend
> >to stop reading.
> 
> I agree 100%.  But excessive brevity can make it so a reader can't
> follow the conversation.  Users of web forums often assume *you* can
> easily look back up the thread because *they* can.  In my experience,
> it isn't always easy to do.

Fortunately, we (postgresql.org) have set up our mailing list archives
so that it _is_ possible to look back entire threads.  Our archives have
proven time and again an extremely valuable resource, and we pride on
their quality and completeness (and the fact that we never ever break
links even when the website is rewritten).  There is always full context
in these lists, if you need it.  

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] psql: do/should we document that argument and option specification order doesn't matter?

[George Neuner]

On Thu, 11 May 2017 16:15:43 -0700, "David G. Johnston"
 wrote:

>I don't know if this applies anywhere else but I just stumbled across the
>fact that our psql documentation is imprecise:
>
>https://www.postgresql.org/docs/current/static/app-psql.html
>
>psql [option...] [dbname [username]]
>
>It does matter that "dbname" precede username; and that it be present if
>username is specified.  But otherwise the first one or two non-option words
>on the command line are taken to be those regardless of position, and any
>extra non-option words are ignored.  Options can thus be specified before,
>after, or in between the dbname and username.
>
>i.e., the following is valid:
>
>psql [dbname] [option...] [# you can place username here but only if dbname
>is specified...]
>
>Ubuntu Bash; 9.5 tested.
>
>I'm not sure how one would actually document the above in a syntax
>specification without being overly verbose but there is no hint that I've
>found pertaining to the true behavior.  Haven't played with any other of
>the supplied binaries; I stumbled across this because I was wrapping psql
>in specialized functions and was surprised that where I placed the "$@"
>and/or the "service=service-name" specification didn't seem to matter.
>
>Is this some general Bash/Linux-ism that I've just never read about or
>realized until now?

The shell expands wildcards before passing them to the application,
but argument handling is completely up to the application.  psql maybe
is more permissive regarding the order than it really needs to be.

I think it would be far easier to change the program to enforce the
documented order than to document the program's current behavior.  

The question is, do we really need to do it?  If one adheres to the
documented ordering, it works.  And if the dbname and username both
are passed as dashed options, there can't be any confusion at all.

There is utility in the naked dbname, but perhaps it's time to lose
the naked username?  How many people actually use that syntax vs some
other method: sudo, .pgpass, environment variables, etc. ?


YMMV,
George



-- 
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] Top posting....

[George Neuner]

On Thu, 11 May 2017 13:43:52 -0400, Tom Lane 
wrote:

>... The point of quoting previous messages is not to replicate
>the entire thread in each message; we have archives for that.  The point
>is to *briefly* remind readers what it is that you're responding to.
>If you can't be brief, you are disrespecting your readers by wasting their
>time. They've probably already read the earlier part of the thread anyway.

Search engines often land in the middle of a conversation.  Quoted
material needs to establish sufficient context for the response to
make sense.

On many occasions, a search has landed me on some site where it was
difficult to navigate threads starting from the middle.

I know we're talking about Usenet here, and Google Groups isn't too
awful[*] when approached strictly as a Usenet archive ... but proper
posting etiquette applies to other discussion mediums as well.


>Personally, when I've scrolled down through a couple of pages of quoted
>and re-quoted text and see no sign of it ending any time soon, I tend
>to stop reading.

I agree 100%.  But excessive brevity can make it so a reader can't
follow the conversation.  Users of web forums often assume *you* can
easily look back up the thread because *they* can.  In my experience,
it isn't always easy to do.


YMMV,
George

[*] where is a "gagging" emoji when you really need one?



-- 
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] psql: do/should we document that argument and option specification order doesn't matter?

[Tom Lane]

"David G. Johnston"  writes:
> I don't know if this applies anywhere else but I just stumbled across the
> fact that our psql documentation is imprecise:

> https://www.postgresql.org/docs/current/static/app-psql.html

> psql [option...] [dbname [username]]

> It does matter that "dbname" precede username; and that it be present if
> username is specified.  But otherwise the first one or two non-option words
> on the command line are taken to be those regardless of position, and any
> extra non-option words are ignored.  Options can thus be specified before,
> after, or in between the dbname and username.

This is, unfortunately, platform-specific.  glibc's version of
getopt_long() takes it upon itself to physically rearrange the argv
list to make such cases work.  On platforms where getopt_long() does
not so blatantly exceed its authority, only the documented argument
order will work.

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

[GENERAL] psql: do/should we document that argument and option specification order doesn't matter?

[David G. Johnston]

I don't know if this applies anywhere else but I just stumbled across the
fact that our psql documentation is imprecise:

https://www.postgresql.org/docs/current/static/app-psql.html

psql [option...] [dbname [username]]

It does matter that "dbname" precede username; and that it be present if
username is specified.  But otherwise the first one or two non-option words
on the command line are taken to be those regardless of position, and any
extra non-option words are ignored.  Options can thus be specified before,
after, or in between the dbname and username.

i.e., the following is valid:

psql [dbname] [option...] [# you can place username here but only if dbname
is specified...]

Ubuntu Bash; 9.5 tested.

I'm not sure how one would actually document the above in a syntax
specification without being overly verbose but there is no hint that I've
found pertaining to the true behavior.  Haven't played with any other of
the supplied binaries; I stumbled across this because I was wrapping psql
in specialized functions and was surprised that where I placed the "$@"
and/or the "service=service-name" specification didn't seem to matter.

Is this some general Bash/Linux-ism that I've just never read about or
realized until now?

David J.

Re: [GENERAL]

[Adrian Klaver]

On 05/11/2017 07:26 AM, Igor Korot wrote:

Adrian et al,

On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
 wrote:

On 05/11/2017 06:24 AM, Igor Korot wrote:



Thank you.
Will take a look and modify to use in my program.

I presume I'm allowed to do that, right?


Yes.




--
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] Request to add feature to the Position function

[Adrian Klaver]

On 05/11/2017 10:55 AM, Ron Ben wrote:

any news on that?



Did you ask on --hackers or file a bug report?



--
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] pglogical and slony

[Armand Pirvu (home)]

Thanks to Adrian I got pointed in the direction of enabling triggers to use 
with pglogical

In other words, whatever comes down the pglogical in terms of 
inserts/updates/deletes, I can track those in some audit table or do something 
else

That got me thinking why not putting at the end of pglogical, Slony  which is 
trigger based ?


Postgres 9.5.5

edb1 192.168.5.251  -   master pglogical
edb2 192.168.5.252  -   slave pglogical


edb2 192.168.5.252  -   master slony
edb3 192.168.5.253  -   slave slony



sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
init cluster (id=1, comment='Node 1');
store node (id=2, comment='Node 2',event node=1);
store path (server=1, client=2, conninfo='dbname=csidb host=192.168.5.252 
user=postgres', connretry=10);
store path (server=2, client=1, conninfo='dbname=csidb host=192.168.5.253 
user=postgres', connretry=10);
create set (id=1, origin=1, comment='some test tables');
set add table (id=1, set id=1, origin=1, fully qualified name='public.tbl2', 
comment='tbl2 table', key='tbl2_pkey');
_EOF_


- master (edb2 192.168.5.252)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb 
user=postgres host=192.168.5.252"
- slave (edb3 192.168.5.253)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb 
user=postgres host=192.168.5.253"


sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
subscribe set (id=1, provider=1, receiver=2, forward=no);
_EOF_



csidb=# \d tbl2;
Table "public.tbl2"
 Column | Type  | Modifiers 
+---+---
 col1   | integer   | not null
 col2   | character(10) | 
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.deny_truncate()
Disabled user triggers:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.log_truncate('1')




csidb=# alter table tbl2 ENABLE ALWAYS TRIGGER _armandpc123_logtrigger;
ALTER TABLE
csidb=# \d tbl2
Table "public.tbl2"
 Column | Type  | Modifiers 
+---+---
 col1   | integer   | not null
 col2   | character varying(10) | 
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.log_truncate('1')
Disabled user triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT 
EXECUTE PROCEDURE _armandpc123.deny_truncate()
Triggers firing always:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
trig_hist_aud_prev_tbl2 AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH 
ROW EXECUTE PROCEDURE func_audit_tname()


csidb=# alter table tbl2 disable trigger _armandpc123_denyaccess;

Manual insert in edb2.csidb.tbl2 replicates fine to edb3.csidb.tbl2, but 
nothing coming in say like replicating from edb1.csidb.tbl2 via pglogical 
although triggers are enabled

Is this possible ? 


Thank you

Armand




-- 
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] Querying a policy

[Jean-Francois Bernier]

Stephen,

Thank you for the quick reply!  This information is very useful to me.

Maybe some more questions will come in the future :)

Thanks again and continue the good work!


-- 
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] Top posting....

[Gavin Flower]

On 12/05/17 05:04, Francisco Olarte wrote:

Slightly unrelated...

On Wed, May 10, 2017 at 11:21 PM, Gavin Flower
 wrote:

It is normal on this list not to top post, but rather to add comments at the
end (so people can see the context) - though interspersed comments in the
body of the text is okay when appropriate!

I'd rather say interspersed comments with the TRIMMED text body is the
appropiate thing to do. Bottom posting ( edited ) being a particular
case of that when only a single topic/question is being answered.

Full quoting ( I mean the people which even quotes others signatures )
is especially ugly, combined with top posting I feel it as insulting (
to me it feels as 'you do not deserve me taking time to edit a bit and
make things clear' ) ( but well, I started when all the university
multiplexed over a 9600bps link, so I may be a bit extreme on this )

Regards.
Francisco Olarte.


Yes I should have mentioned trimming - but in my defence, I did using 
trimming in my reply!


I started using the internet when I had a 2400 bps modem, in 1990.

I introduced the use of "[...]" to replace the then common "[ omitted ]" 
which was being used in usenet - my very small part in Internet history.



Cheers,
Gavin



--
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]

[Neil Anderson]

>
> Like I said, what I expect to see from the query is:
>
> id | integer | | 5| 2 | 0 | P |
> name | varchar | 50| 2 | | | | 
>
> So I need the information about the field and whether the field is a
> primary/foreign key or not.
>

I had a go at it using the catalog tables from v9.5 and an example
table 'films', maybe you can extend this further to get what you need
from the pg_attribute, pg_class, pg_type and pg_constraint tables?

SELECT columns.attname as name,
data_types.typname as type,
columns.attlen as length,
columns.attnotnull as not_null,
constraints.contype
FROM pg_attribute columns
INNER JOIN pg_class tables ON columns.attrelid = tables.oid
INNER JOIN pg_type data_types ON columns.atttypid = data_types.oid
LEFT JOIN pg_constraint constraints
ON constraints.conrelid = columns.attrelid AND columns.attnum = ANY
(constraints.conkey)
WHERE tables.relname = 'films' AND columns.attnum > 0;

Thanks,
Neil

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Constraints of view attributes

[Tom Lane]

<51183...@gmx.at> writes:
> create table t1 (f1 text not null); 
> create table t2 (f2 text); 
> create view v as (select t1.f1, t2.f2 from t1, t2); 

> Given v, How can a find out the t1.f1 has a not null constraint. 

I assume what you actually mean is you want to know whether the
view v will always produce a non-null in that column.  That's not
that easy.  It is true in the above example, but consider for instance

create view v as select t1.f1, t2.f2 from t2 left join t1 on ...

With the outer join, that view column could produce nulls despite
the NOT NULL constraint on t1.  There are other constructs such
as GROUPING SETS that break the equivalence, too.  So you'd need a
fairly careful inspection of the view parsetree not only to find
which table is referenced, but whether there's something in the
view that defeats the deduction you'd like to make.  There isn't
anything in Postgres right now that makes that type of inference,
let alone a way to export it to userland.

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] Constraints of view attributes

[Adrian Klaver]

On 05/11/2017 12:24 PM, 51183...@gmx.at wrote:

Hi,

create table t1 (f1 text not null);
create table t2 (f2 text);
create view v as (select t1.f1, t2.f2 from t1, t2);

Given v, How can a find out the t1.f1 has a not null constraint.
I can easily find this for t1 in the system catalog, but not for v.
I learned on IRC today that this should be somewhere in pg_rewrite but
where exactly and how do I get this information? Parse rewrite
query_tree? How?


SELECT
table_name, column_name, is_nullable
FROM
information_schema.columns
WHERE
table_name IN (
SELECT
table_name
FROM
information_schema.view_column_usage
WHERE
view_name = 'v'
AND column_name = 'f1')
AND column_name = 'f1';

 table_name | column_name | is_nullable
+-+-
 t1 | f1  | NO



--
Greg





--
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] Constraints of view attributes

[51183341]

Hi,

create table t1 (f1 text not null); 
create table t2 (f2 text); 
create view v as (select t1.f1, t2.f2 from t1, t2); 

Given v, How can a find out the t1.f1 has a not null constraint. 
I can easily find this for t1 in the system catalog, but not for v.
I learned on IRC today that this should be somewhere in pg_rewrite but
where exactly and how do I get this information? Parse rewrite 
query_tree? How?

--
Greg


-- 
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] How to recover my postgres database ?

[Tom Lane]

Pierre Couderc  writes:
> Mmm, there is some misunderstanding. I have lost nothing.
> All my DBs are ok.
> I am speaking of the specific "postgres" database that you connect to with
> \c postgres
> What is its use ? how to restore it ?

If you know that there should be nothing non-default in the postgres
database, it should be fine to just drop it and recreate it.  It's
not magic, it's just a default landing point for connections.

If there is stuff you keep there, probably restoring that stuff
from your last backup is the best you're going to be able to do.

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] Top posting....

[Tom Lane]

Francisco Olarte  writes:
> Slightly unrelated...
> On Wed, May 10, 2017 at 11:21 PM, Gavin Flower
>  wrote:
>> It is normal on this list not to top post, but rather to add comments at the
>> end (so people can see the context) - though interspersed comments in the
>> body of the text is okay when appropriate!

> I'd rather say interspersed comments with the TRIMMED text body is the
> appropiate thing to do.

Absolutely.  The point of quoting previous messages is not to replicate
the entire thread in each message; we have archives for that.  The point
is to *briefly* remind readers what it is that you're responding to.
If you can't be brief, you are disrespecting your readers by wasting their
time. They've probably already read the earlier part of the thread anyway.

Personally, when I've scrolled down through a couple of pages of quoted
and re-quoted text and see no sign of it ending any time soon, I tend
to stop reading.

Another point is to please put a blank line or so between quoted text
and your own comment.  If you don't provide that visual separation,
you're again making it hard for readers to find what you're adding.

Getting a bit more philosophical: top-posting and not bothering to
trim the quoted material actually work fine together.  You're putting
more cognitive burden on the readers if they don't already remember
what the discussion is, but if you're responding to a five-minute-old
message that probably isn't an issue.  The trim-quotes-and-reply-below
style evolved for discussions that might last over days, where readers
can benefit from a quick reminder.  Bottom posting without trimming
is just an awful combination: whatever you do, don't do that.

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

[GENERAL] File hashes for downloads

[Evan Blackstone]

Needing to get the ODBC driver (downloading it from here 
https://www.postgresql.org/ftp/odbc/versions/msi/) , but I can't find any 
hashes to verify my download. Is there a list somewhere that I'm missing?


Thank you,

Evan Blackstone
Oklahoma Employees Credit Union
Information Security Officer
Phone: 405.606.6369
Mobile: 405.669.1638
Fax: 405.606.6345
evan.blackst...@oecu.org 
www.oecu.org 
[cid:image001.jpg@01D2CA51.965350D0][cid:image002.jpg@01D2CA51.965350D0][cid:image003.jpg@01D2CA51.965350D0][cid:image004.jpg@01D2CA51.965350D0][cid:image005.jpg@01D2CA51.965350D0]




NOTICE:
This e-mail is intended solely for the use of the individual to whom it is 
addressed and may contain information that is privileged, confidential or 
otherwise exempt from disclosure. If the reader of this e-mail is not the 
intended recipient or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, or copying of this communication is strictly 
prohibited. If you have received this communication in error, please 
immediately notify us by replying to the original message at the listed email 
address.

Thank you
Oklahoma Employees Credit Union
http://www.oecu.org

Re: [GENERAL] How to recover my postgres database ?

[Pierre Couderc]

On 05/11/2017 07:10 PM, John R Pierce wrote:

On 5/11/2017 9:53 AM, Pierre Couderc wrote:

I have broken my postgres database by typing :

psql How can I safely repair, knowing that I have the pg_dumpall of last  
night, but many dbs have changed today... ?


Thanks in advance 



was there anything in the postgres database other than the default?

Not at my knowledge...


psql template1 -c "drop database postgres; create database postgres 
with template template0"



should restore it to a virgin stock empty 'postgres'


Thank you, I do that. This is what I looked for !


--
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] How to recover my postgres database ?

[John R Pierce]

On 5/11/2017 9:53 AM, Pierre Couderc wrote:
I have the pg_dumpall of last  night, but many dbs have changed 
today... ? 


suggestion in the future, instead of simply pg_dumpall, where all your 
databases are in one opaque lump, try something like...


#!/bin/bash
#
d=`date +\%a`
dst=/home2/backups/pgsql
/usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip > 
$dst/pgdumpall.globals.$d.sql.gz
for i in $(psql -tc "select datname from pg_database where not 
datistemplate"); do pg_dump -Fc -f $dst/pgdump.$i.$d.dump $i

done


which uses pg_dumpall to dump the globals only into one file, then uses 
pg_dump -Fc to create compressed format dumps of each individual 
database, these can be selectively restored with pg_restore (for 
instance, you could restore just one table, or schema only, or data 
only, etcetc).that script is setup to create a different set of 
files for each day of the week, so you have 7 days backup history, 
change the parameter of the d=`date...  line if you want a different 
backup rotation scheme, and of course, dst is the destination




--
john r pierce, recycling bits in santa cruz



--
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] Top posting....

[John McKown]

On Thu, May 11, 2017 at 12:04 PM, Francisco Olarte 
wrote:

> Slightly unrelated...
>
> ​
>
>
> Full quoting ( I mean the people which even quotes others signatures )
> is especially ugly, combined with top posting I feel it as insulting (
> to me it feels as 'you do not deserve me taking time to edit a bit and
> make things clear' ) ( but well, I started when all the university
> multiplexed over a 9600bps link, so I may be a bit extreme on this )
>

​I feel the same way. Because I started out long ago with a 300bps acoustic
modem with a _dial_ phone!​



>
> Regards.
> Francisco Olarte.
>


-- 
Advertising is a valuable economic factor because it is the cheapest way of
selling goods, particularly if the goods are worthless. -- Sinclair Lewis


Maranatha! <><
John McKown

Re: [GENERAL] How to recover my postgres database ?

[Pierre Couderc]

On 05/11/2017 07:07 PM, Hunley, Douglas wrote:


On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc > wrote:


How can I safely repair, knowing that I have the pg_dumpall of
last  night, but many dbs have changed today... ?


If pg_dumpall is your only backup mechanism then you've lost all 
changes after the dump was taken. You'll need to restore from that 
backup and then get pgbackrest (or another equivalent tool) up and 
running to protect yourself going forward



Mmm, there is some misunderstanding. I have lost nothing.
All my DBs are ok.
I am speaking of the specific "postgres" database that you connect to with
\c postgres
What is its use ? how to restore it ?

Re: [GENERAL] How to recover my postgres database ?

[John R Pierce]

On 5/11/2017 9:53 AM, Pierre Couderc wrote:

I have broken my postgres database by typing :

psql How can I safely repair, knowing that I have the pg_dumpall of last  
night, but many dbs have changed today... ?


Thanks in advance 



was there anything in the postgres database other than the default?

psql template1 -c "drop database postgres; create database postgres with 
template template0"



should restore it to a virgin stock empty 'postgres'


--
john r pierce, recycling bits in santa cruz



--
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] How to recover my postgres database ?

[Hunley, Douglas]

On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc  wrote:

> How can I safely repair, knowing that I have the pg_dumpall of last
> night, but many dbs have changed today... ?
>

If pg_dumpall is your only backup mechanism then you've lost all changes
after the dump was taken. You'll need to restore from that backup and then
get pgbackrest (or another equivalent tool) up and running to protect
yourself going forward


-- 
{
  "name" : "douglas j hunley",
  "title" : "database engineer",
  "email" : "douglas.hun...@openscg.com ",
  "mobile" : "+1 614 316 5079"
}

[GENERAL] Top posting....

[Francisco Olarte]

Slightly unrelated...

On Wed, May 10, 2017 at 11:21 PM, Gavin Flower
 wrote:
> It is normal on this list not to top post, but rather to add comments at the
> end (so people can see the context) - though interspersed comments in the
> body of the text is okay when appropriate!

I'd rather say interspersed comments with the TRIMMED text body is the
appropiate thing to do. Bottom posting ( edited ) being a particular
case of that when only a single topic/question is being answered.

Full quoting ( I mean the people which even quotes others signatures )
is especially ugly, combined with top posting I feel it as insulting (
to me it feels as 'you do not deserve me taking time to edit a bit and
make things clear' ) ( but well, I started when all the university
multiplexed over a 9600bps link, so I may be a bit extreme on this )

Regards.
Francisco Olarte.


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

[GENERAL] How to recover my postgres database ?

[Pierre Couderc]

I have broken my postgres database by typing :

psql How can I safely repair, knowing that I have the pg_dumpall of last  
night, but many dbs have changed today... ?


Thanks in advance




--
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] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Vibhor Kumar]

> On May 11, 2017, at 10:40 AM, Adrian Klaver  wrote:
> 
> On 05/11/2017 07:32 AM, Vibhor Kumar wrote:
>>> On May 11, 2017, at 10:17 AM, Adrian Klaver >>  >> >> wrote:
>>> 
> 
>>> Not sure how deep you are into your present situation. The only thing I can 
>>> think of is to install Postgres using the PGDG repos and then migrating the 
>>> data from the EDB install to the RPM install. You would probably have to 
>>> change the port numbers, at least temporarily, while you do the migration. 
>>> This way you could use the packaging system without resorting to 
>>> work-arounds.
>> I don’t think Martin has to migrate database. He has installed  > PostgreSQL 
>> 9.6 using EDB one-click installer. I can see two options here:
>> 1. Install PostgreSQL using RPM as mentioned by Adrian. Stop the PostgreSQL 
>> using one click installer binaries and start using RPM binary. After that 
>> Martin can install repmgr using RPM.
> 
> From OP:
> "I have now been tasked with putting repmgr onto this box and converting it 
> from a standalone to a multiple node setup (I have two additional servers 
> lurking in the background ready for use ). "
> 
> That to me says there is data in the clusters that would need to be migrated 
> from the EDB instance(s) to the RPM instance(s). That could be as simple as a 
> dump/restore.



Based on pg_config, I can see following:
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz 
-ledit -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6.2

Since, its PostgreSQL 9.6.2, he can simply install RPM and restart the 
PostgreSQL using RPM binary on same data directory. If Martin is using 
EnterpriseDB Advanced server, then only I can see he has to migrate.

Thanks & Regards,
Vibhor Kumar
Blogs:https://vibhorkumar.blog

Re: [GENERAL] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Andreas Kretschmer]

Am 11.05.2017 um 15:58 schrieb Martin Goodson:

Hello. First time poster, so please be gentle :)

I have a PostgreSQL 9.6 database cluster running on a standalone 
Redhat 7.2 (Maipo) server.


This may seem like a silly question. It probably *is* a silly 
question, so apologies in advance.


PostgreSQL was installed using the "EDB Postgres Standard" installer 
from EnterpriseDB - 
https://www.enterprisedb.com/software-downloads-postgres - and 
installed to a specific location - /db_.  No repositories, 
nothing in a 'standard' place.



I have now been tasked with putting repmgr onto this box and 
converting it from a standalone to a multiple node setup (I have two 
additional servers lurking in the background ready for use :) ).


Numerous articles on repmgr all seem to suggest it should be a 
relatively simply operation once the software is in place. Register 
the master. Clone standbys. Ought to be *relatively* straight-forward.


Unfortunately, I'm having problems at the first hurdle - putting the 
software in place. We initially tried installing from the postgres 
repository rpm - which insisted, of course, on installing PostgreSQL 
as well as repmgr in /usr/pgsql-9.6. Obviously not the right place for 
it given we're running our binaries from 
/db_dbname/app/postgres/9.6.2-3/bin


Is there a simple way to use repmgr from the package, perhaps by 
copying/linking files from that location to ours? (e.g. 
copying/linking some files from lib, bin, contrib, etc).




I think, repmgr *should* work with EDB-Version of PostgreSQL. You can 
set pg_bindir in repmgr.conf. I would also suggest create symlinks in 
/etc to the repmgr.conf.


Btw.: you also set

|service_start_command service_stop_command service_restart_command 
service_reload_command service_promote_command|




Keep me informed if that works, thx.


Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: [GENERAL] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Adrian Klaver]

On 05/11/2017 07:32 AM, Vibhor Kumar wrote:


On May 11, 2017, at 10:17 AM, Adrian Klaver > wrote:




Not sure how deep you are into your present situation. The only thing 
I can think of is to install Postgres using the PGDG repos and then 
migrating the data from the EDB install to the RPM install. You would 
probably have to change the port numbers, at least temporarily, while 
you do the migration. This way you could use the packaging system 
without resorting to work-arounds.


I don’t think Martin has to migrate database. He has installed  > PostgreSQL 
9.6 using EDB one-click installer. I can see two options here:
1. Install PostgreSQL using RPM as mentioned by Adrian. Stop the 
PostgreSQL using one click installer binaries and start using RPM 
binary. After that Martin can install repmgr using RPM.


From OP:
"I have now been tasked with putting repmgr onto this box and converting 
it from a standalone to a multiple node setup (I have two additional 
servers lurking in the background ready for use ). "


That to me says there is data in the clusters that would need to be 
migrated from the EDB instance(s) to the RPM instance(s). That could be 
as simple as a dump/restore.



2. Compile repmgr with existing installation.
https://github.com/2ndQuadrant/repmgr

Thanks & Regards,
Vibhor Kumar
Blogs:https://vibhorkumar.blog




--
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] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Vibhor Kumar]

> On May 11, 2017, at 10:17 AM, Adrian Klaver  wrote:
> 
> On 05/11/2017 06:58 AM, Martin Goodson wrote:
>> Hello. First time poster, so please be gentle :)
>> I have a PostgreSQL 9.6 database cluster running on a standalone Redhat 7.2 
>> (Maipo) server.
>> This may seem like a silly question. It probably *is* a silly question, so 
>> apologies in advance.
>> PostgreSQL was installed using the "EDB Postgres Standard" installer from 
>> EnterpriseDB - https://www.enterprisedb.com/software-downloads-postgres - 
>> and installed to a specific location - /db_.  No repositories, 
>> nothing in a 'standard' place.
>> Output of pg_config is as follows:
>> BINDIR = /db_dbname/app/postgres/9.6.2-3/bin
>> DOCDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
>> HTMLDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
>> INCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include
>> PKGINCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include/postgresql
>> INCLUDEDIR-SERVER = /db_dbname/app/postgres/9.6.2-3/include/postgresql/server
>> LIBDIR = /db_dbname/app/postgres/9.6.2-3/lib
>> PKGLIBDIR = /db_dbname/app/postgres/9.6.2-3/lib/postgresql
>> LOCALEDIR = /db_dbname/app/postgres/9.6.2-3/share/locale
>> MANDIR = /db_dbname/app/postgres/9.6.2-3/share/man
>> SHAREDIR = /db_dbname/app/postgres/9.6.2-3/share/postgresql
>> SYSCONFDIR = /db_dbname/app/postgres/9.6.2-3/etc/postgresql
>> PGXS = 
>> /db_dbname/app/postgres/9.6.2-3/lib/postgresql/pgxs/src/makefiles/pgxs.mk
>> CONFIGURE = '--enable-debug' '--with-libs=/opt/local/Current/lib' 
>> '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include'
>>  '--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' 
>> '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' 
>> '--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.6/Tcl-8.5/lib' 
>> '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' 
>> '--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql'
>>  '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 
>> 'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-O2 -DMAP_HUGETLB=0x4'
>> CC = gcc
>> CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 
>> -I/opt/local/Current/include
>> CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
>> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
>> -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x4
>> CFLAGS_SL = -fpic
>> LDFLAGS = -L../../src/common -L/opt/local/Current/lib -Wl,--as-needed 
>> -Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags
>>  LDFLAGS_EX =
>> LDFLAGS_SL =
>> LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 
>> -lz -ledit -lrt -lcrypt -ldl -lm
>> VERSION = PostgreSQL 9.6.2
>> I have now been tasked with putting repmgr onto this box and converting it 
>> from a standalone to a multiple node setup (I have two additional servers 
>> lurking in the background ready for use :) ).
>> Numerous articles on repmgr all seem to suggest it should be a relatively 
>> simply operation once the software is in place. Register the master. Clone 
>> standbys. Ought to be *relatively* straight-forward.
>> Unfortunately, I'm having problems at the first hurdle - putting the 
>> software in place. We initially tried installing from the postgres 
>> repository rpm - which insisted, of course, on installing PostgreSQL as well 
>> as repmgr in /usr/pgsql-9.6. Obviously not the right place for it given 
>> we're running our binaries from /db_dbname/app/postgres/9.6.2-3/bin
>> Is there a simple way to use repmgr from the package, perhaps by 
>> copying/linking files from that location to ours? (e.g. copying/linking some 
>> files from lib, bin, contrib, etc).
>> Or am I looking at having to compile the source? I'm told it should be 
>> 'simple' but I've not compiled much software from scratch on linux, so I'm a 
>> bit nervous about the prospect. Any advice would be welcome :)
>> Basically, am I going to be stuck compiling from source or is there a way 
>> around this?
> 
> Not sure how deep you are into your present situation. The only thing I can 
> think of is to install Postgres using the PGDG repos and then migrating the 
> data from the EDB install to the RPM install. You would probably have to 
> change the port numbers, at least temporarily, while you do the migration. 
> This way you could use the packaging system without resorting to work-arounds.


I don’t think Martin has to migrate database. He has installed PostgreSQL 9.6 
using EDB one-click installer. I can see two options here:
1. Install PostgreSQL using RPM as mentioned by Adrian. Stop the PostgreSQL 
using one click installer binaries and start using RPM binary. After that 
Martin can install repmgr using RPM.
2. Compile repmgr with existing installation.
https://github.com/2ndQuadrant/repmgr 

Re: [GENERAL]

[Igor Korot]

Adrian et al,

On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
 wrote:
> On 05/11/2017 06:24 AM, Igor Korot wrote:
>>
>> Melvin et al,
>>
>
>>
>> Now are you saying that the information about the fields in the table can
>> be retrieved from
>> system catalog? Or are you saying that retrieving everything in one shot
>
>
> As Melvin and others have mentioned that is where the information_schema.*
> get their data. If you want to see how it is done as a way of possibly
> customizing for your own use see in the source code:
>
> src/backend/catalog/information_schema.sql
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed88d5801716eb01cf28b6b5be2b5cd

Thank you.
Will take a look and modify to use in my program.

I presume I'm allowed to do that, right?

>
>
>> is not possible?
>>
>> Thank you.
>
>
>
>
> --
> 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] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Adrian Klaver]

On 05/11/2017 06:58 AM, Martin Goodson wrote:

Hello. First time poster, so please be gentle :)

I have a PostgreSQL 9.6 database cluster running on a standalone Redhat 
7.2 (Maipo) server.


This may seem like a silly question. It probably *is* a silly question, 
so apologies in advance.


PostgreSQL was installed using the "EDB Postgres Standard" installer 
from EnterpriseDB - 
https://www.enterprisedb.com/software-downloads-postgres - and installed 
to a specific location - /db_.  No repositories, nothing in a 
'standard' place.


Output of pg_config is as follows:

BINDIR = /db_dbname/app/postgres/9.6.2-3/bin
DOCDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
HTMLDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
INCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include
PKGINCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include/postgresql
INCLUDEDIR-SERVER = 
/db_dbname/app/postgres/9.6.2-3/include/postgresql/server

LIBDIR = /db_dbname/app/postgres/9.6.2-3/lib
PKGLIBDIR = /db_dbname/app/postgres/9.6.2-3/lib/postgresql
LOCALEDIR = /db_dbname/app/postgres/9.6.2-3/share/locale
MANDIR = /db_dbname/app/postgres/9.6.2-3/share/man
SHAREDIR = /db_dbname/app/postgres/9.6.2-3/share/postgresql
SYSCONFDIR = /db_dbname/app/postgres/9.6.2-3/etc/postgresql
PGXS = 
/db_dbname/app/postgres/9.6.2-3/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-debug' '--with-libs=/opt/local/Current/lib' 
'--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include' 
'--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' 
'--with-ldap' '--with-openssl' '--with-perl' '--with-python' 
'--with-tcl' 
'--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.6/Tcl-8.5/lib' 
'--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' 
'--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql' 
'--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 
'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-O2 -DMAP_HUGETLB=0x4'

CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x4

CFLAGS_SL = -fpic
LDFLAGS = -L../../src/common -L/opt/local/Current/lib -Wl,--as-needed 
-Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags 


LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto 
-lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm

VERSION = PostgreSQL 9.6.2

I have now been tasked with putting repmgr onto this box and converting 
it from a standalone to a multiple node setup (I have two additional 
servers lurking in the background ready for use :) ).


Numerous articles on repmgr all seem to suggest it should be a 
relatively simply operation once the software is in place. Register the 
master. Clone standbys. Ought to be *relatively* straight-forward.


Unfortunately, I'm having problems at the first hurdle - putting the 
software in place. We initially tried installing from the postgres 
repository rpm - which insisted, of course, on installing PostgreSQL as 
well as repmgr in /usr/pgsql-9.6. Obviously not the right place for it 
given we're running our binaries from /db_dbname/app/postgres/9.6.2-3/bin


Is there a simple way to use repmgr from the package, perhaps by 
copying/linking files from that location to ours? (e.g. copying/linking 
some files from lib, bin, contrib, etc).


Or am I looking at having to compile the source? I'm told it should be 
'simple' but I've not compiled much software from scratch on linux, so 
I'm a bit nervous about the prospect. Any advice would be welcome :)


Basically, am I going to be stuck compiling from source or is there a 
way around this?


Not sure how deep you are into your present situation. The only thing I 
can think of is to install Postgres using the PGDG repos and then 
migrating the data from the EDB install to the RPM install. You would 
probably have to change the port numbers, at least temporarily, while 
you do the migration. This way you could use the packaging system 
without resorting to work-arounds.




Many thanks.

Martin.



--
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] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

[Martin Goodson]

Hello. First time poster, so please be gentle :)

I have a PostgreSQL 9.6 database cluster running on a standalone Redhat 
7.2 (Maipo) server.


This may seem like a silly question. It probably *is* a silly question, 
so apologies in advance.


PostgreSQL was installed using the "EDB Postgres Standard" installer 
from EnterpriseDB - 
https://www.enterprisedb.com/software-downloads-postgres - and installed 
to a specific location - /db_.  No repositories, nothing in a 
'standard' place.


Output of pg_config is as follows:

BINDIR = /db_dbname/app/postgres/9.6.2-3/bin
DOCDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
HTMLDIR = /db_dbname/app/postgres/9.6.2-3/doc/postgresql
INCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include
PKGINCLUDEDIR = /db_dbname/app/postgres/9.6.2-3/include/postgresql
INCLUDEDIR-SERVER = 
/db_dbname/app/postgres/9.6.2-3/include/postgresql/server

LIBDIR = /db_dbname/app/postgres/9.6.2-3/lib
PKGLIBDIR = /db_dbname/app/postgres/9.6.2-3/lib/postgresql
LOCALEDIR = /db_dbname/app/postgres/9.6.2-3/share/locale
MANDIR = /db_dbname/app/postgres/9.6.2-3/share/man
SHAREDIR = /db_dbname/app/postgres/9.6.2-3/share/postgresql
SYSCONFDIR = /db_dbname/app/postgres/9.6.2-3/etc/postgresql
PGXS = 
/db_dbname/app/postgres/9.6.2-3/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-debug' '--with-libs=/opt/local/Current/lib' 
'--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include' 
'--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' 
'--with-ldap' '--with-openssl' '--with-perl' '--with-python' 
'--with-tcl' 
'--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.6/Tcl-8.5/lib' 
'--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' 
'--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql' 
'--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 
'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-O2 -DMAP_HUGETLB=0x4'

CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x4

CFLAGS_SL = -fpic
LDFLAGS = -L../../src/common -L/opt/local/Current/lib -Wl,--as-needed 
-Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags

LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto 
-lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm

VERSION = PostgreSQL 9.6.2

I have now been tasked with putting repmgr onto this box and converting 
it from a standalone to a multiple node setup (I have two additional 
servers lurking in the background ready for use :) ).


Numerous articles on repmgr all seem to suggest it should be a 
relatively simply operation once the software is in place. Register the 
master. Clone standbys. Ought to be *relatively* straight-forward.


Unfortunately, I'm having problems at the first hurdle - putting the 
software in place. We initially tried installing from the postgres 
repository rpm - which insisted, of course, on installing PostgreSQL as 
well as repmgr in /usr/pgsql-9.6. Obviously not the right place for it 
given we're running our binaries from /db_dbname/app/postgres/9.6.2-3/bin


Is there a simple way to use repmgr from the package, perhaps by 
copying/linking files from that location to ours? (e.g. copying/linking 
some files from lib, bin, contrib, etc).


Or am I looking at having to compile the source? I'm told it should be 
'simple' but I've not compiled much software from scratch on linux, so 
I'm a bit nervous about the prospect. Any advice would be welcome :)


Basically, am I going to be stuck compiling from source or is there a 
way around this?


Many thanks.

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."


--
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]

[Adrian Klaver]

On 05/11/2017 06:24 AM, Igor Korot wrote:

Melvin et al,





Now are you saying that the information about the fields in the table 
can be retrieved from
system catalog? Or are you saying that retrieving everything in one shot 


As Melvin and others have mentioned that is where the 
information_schema.* get their data. If you want to see how it is done 
as a way of possibly customizing for your own use see in the source code:


src/backend/catalog/information_schema.sql

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed88d5801716eb01cf28b6b5be2b5cd


is not possible?

Thank you.




--
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]

[Melvin Davidson]

On Thu, May 11, 2017 at 9:24 AM, Igor Korot  wrote:

> Melvin et al,
>
> On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson 
> wrote:
>
>>
>> On Wed, May 10, 2017 at 11:26 PM, Igor Korot  wrote:
>>
>>> Hi, John et al,
>>>
>>> On Wed, May 10, 2017 at 11:02 PM, John R Pierce 
>>> wrote:
>>> > On 5/10/2017 7:45 PM, Igor Korot wrote:
>>> >>
>>> >> I found
>>> >> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>>> >> but now I need
>>> >> to connect this with information_schema.columns.
>>> >>
>>> >> What is best way to do it?
>>> >>
>>> >> Or maybe that query I referenced is completely wrong?
>>> >
>>> >
>>> >
>>> > if you're using pg_catalog stuff there's little point in using the
>>> > information_schema views, which exist for compatability with the SQL
>>> > standard.
>>> >
>>> > information_schema.columns is a view, like...
>>>
>>> Like I said, what I expect to see from the query is:
>>>
>>> id | integer | | 5| 2 | 0 | P |
>>> name | varchar | 50| 2 | | | | 
>>>
>>> So I need the information about the field and whether the field is a
>>> primary/foreign key or not.
>>>
>>> And this is according to the schema.table.
>>>
>>> Thank you.
>>>
>>> >
>>> > View definition:
>>> >  SELECT current_database()::information_schema.sql_identifier AS
>>> > table_catalog,
>>> > nc.nspname::information_schema.sql_identifier AS table_schema,
>>> > c.relname::information_schema.sql_identifier AS table_name,
>>> > a.attname::information_schema.sql_identifier AS column_name,
>>> > a.attnum::information_schema.cardinal_number AS ordinal_position,
>>> > pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data
>>> AS
>>> > column_default,
>>> > CASE
>>> > WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
>>> t.typnotnull
>>> > THEN 'NO'::text
>>> > ELSE 'YES'::text
>>> > END::information_schema.yes_or_no AS is_nullable,
>>> > CASE
>>> > WHEN t.typtype = 'd'::"char" THEN
>>> > CASE
>>> > WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
>>> > 'ARRAY'::text
>>> > WHEN nbt.nspname = 'pg_catalog'::name THEN
>>> > format_type(t.typbasetype, NULL::integer)
>>> > ELSE 'USER-DEFINED'::text
>>> > END
>>> > ELSE
>>> > CASE
>>> > WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
>>> > 'ARRAY'::text
>>> > WHEN nt.nspname = 'pg_catalog'::name THEN
>>> > format_type(a.atttypid, NULL::integer)
>>> > ELSE 'USER-DEFINED'::text
>>> > END
>>> > END::information_schema.character_data AS data_type,
>>> > information_schema._pg_char_max_length(information_schema._p
>>> g_truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.cardinal_numb
>>> > er AS character_maximum_length,
>>> > information_schema._pg_char_octet_length(information_schema.
>>> _pg_truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.cardinal_nu
>>> > mber AS character_octet_length,
>>> > information_schema._pg_numeric_precision(information_schema.
>>> _pg_truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.cardinal_nu
>>> > mber AS numeric_precision,
>>> > information_schema._pg_numeric_precision_radix(information_s
>>> chema._pg_truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> t.*))::information_schema.cardi
>>> > nal_number AS numeric_precision_radix,
>>> > information_schema._pg_numeric_scale(information_schema._pg_
>>> truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.cardinal_number
>>> >  AS numeric_scale,
>>> > information_schema._pg_datetime_precision(information_schema
>>> ._pg_truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.cardinal_n
>>> > umber AS datetime_precision,
>>> > information_schema._pg_interval_type(information_schema._pg_
>>> truetypid(a.*,
>>> > t.*), information_schema._pg_truetypmod(a.*,
>>> > t.*))::information_schema.character_data
>>> > AS interval_type,
>>> > NULL::integer::information_schema.cardinal_number AS
>>> interval_precision,
>>> > NULL::character varying::information_schema.sql_identifier AS
>>> > character_set_catalog,
>>> > NULL::character varying::information_schema.sql_identifier AS
>>> > character_set_schema,
>>> > NULL::character varying::information_schema.sql_identifier AS
>>> > character_set_name,
>>> > CASE
>>> > WHEN nco.nspname IS NOT NULL THEN current_database()
>>> > ELSE NULL::name
>>> > END::information_schema.sql_identifier AS collation_catalog,
>>> > nco.nspname::information_schema.sql_identifier AS
>>> collation_schema,
>>> > 

Re: [GENERAL]

[Igor Korot]

Melvin et al,

On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson 
wrote:

>
> On Wed, May 10, 2017 at 11:26 PM, Igor Korot  wrote:
>
>> Hi, John et al,
>>
>> On Wed, May 10, 2017 at 11:02 PM, John R Pierce 
>> wrote:
>> > On 5/10/2017 7:45 PM, Igor Korot wrote:
>> >>
>> >> I found
>> >> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>> >> but now I need
>> >> to connect this with information_schema.columns.
>> >>
>> >> What is best way to do it?
>> >>
>> >> Or maybe that query I referenced is completely wrong?
>> >
>> >
>> >
>> > if you're using pg_catalog stuff there's little point in using the
>> > information_schema views, which exist for compatability with the SQL
>> > standard.
>> >
>> > information_schema.columns is a view, like...
>>
>> Like I said, what I expect to see from the query is:
>>
>> id | integer | | 5| 2 | 0 | P |
>> name | varchar | 50| 2 | | | | 
>>
>> So I need the information about the field and whether the field is a
>> primary/foreign key or not.
>>
>> And this is according to the schema.table.
>>
>> Thank you.
>>
>> >
>> > View definition:
>> >  SELECT current_database()::information_schema.sql_identifier AS
>> > table_catalog,
>> > nc.nspname::information_schema.sql_identifier AS table_schema,
>> > c.relname::information_schema.sql_identifier AS table_name,
>> > a.attname::information_schema.sql_identifier AS column_name,
>> > a.attnum::information_schema.cardinal_number AS ordinal_position,
>> > pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data
>> AS
>> > column_default,
>> > CASE
>> > WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
>> t.typnotnull
>> > THEN 'NO'::text
>> > ELSE 'YES'::text
>> > END::information_schema.yes_or_no AS is_nullable,
>> > CASE
>> > WHEN t.typtype = 'd'::"char" THEN
>> > CASE
>> > WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
>> > 'ARRAY'::text
>> > WHEN nbt.nspname = 'pg_catalog'::name THEN
>> > format_type(t.typbasetype, NULL::integer)
>> > ELSE 'USER-DEFINED'::text
>> > END
>> > ELSE
>> > CASE
>> > WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
>> > 'ARRAY'::text
>> > WHEN nt.nspname = 'pg_catalog'::name THEN
>> > format_type(a.atttypid, NULL::integer)
>> > ELSE 'USER-DEFINED'::text
>> > END
>> > END::information_schema.character_data AS data_type,
>> > information_schema._pg_char_max_length(information_schema._
>> pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_numb
>> > er AS character_maximum_length,
>> > information_schema._pg_char_octet_length(information_schema.
>> _pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_nu
>> > mber AS character_octet_length,
>> > information_schema._pg_numeric_precision(information_schema.
>> _pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_nu
>> > mber AS numeric_precision,
>> > information_schema._pg_numeric_precision_radix(information_
>> schema._pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> t.*))::information_schema.cardi
>> > nal_number AS numeric_precision_radix,
>> > information_schema._pg_numeric_scale(information_schema._pg_
>> truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_number
>> >  AS numeric_scale,
>> > information_schema._pg_datetime_precision(information_
>> schema._pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_n
>> > umber AS datetime_precision,
>> > information_schema._pg_interval_type(information_schema._pg_
>> truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.character_data
>> > AS interval_type,
>> > NULL::integer::information_schema.cardinal_number AS
>> interval_precision,
>> > NULL::character varying::information_schema.sql_identifier AS
>> > character_set_catalog,
>> > NULL::character varying::information_schema.sql_identifier AS
>> > character_set_schema,
>> > NULL::character varying::information_schema.sql_identifier AS
>> > character_set_name,
>> > CASE
>> > WHEN nco.nspname IS NOT NULL THEN current_database()
>> > ELSE NULL::name
>> > END::information_schema.sql_identifier AS collation_catalog,
>> > nco.nspname::information_schema.sql_identifier AS collation_schema,
>> > co.collname::information_schema.sql_identifier AS collation_name,
>> > CASE
>> > WHEN t.typtype = 'd'::"char" THEN current_database()
>> > ELSE NULL::name
>> > END::information_schema.sql_identifier AS 

Re: [GENERAL]

[Melvin Davidson]

On Wed, May 10, 2017 at 11:26 PM, Igor Korot  wrote:

> Hi, John et al,
>
> On Wed, May 10, 2017 at 11:02 PM, John R Pierce 
> wrote:
> > On 5/10/2017 7:45 PM, Igor Korot wrote:
> >>
> >> I found
> >> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
> >> but now I need
> >> to connect this with information_schema.columns.
> >>
> >> What is best way to do it?
> >>
> >> Or maybe that query I referenced is completely wrong?
> >
> >
> >
> > if you're using pg_catalog stuff there's little point in using the
> > information_schema views, which exist for compatability with the SQL
> > standard.
> >
> > information_schema.columns is a view, like...
>
> Like I said, what I expect to see from the query is:
>
> id | integer | | 5| 2 | 0 | P |
> name | varchar | 50| 2 | | | | 
>
> So I need the information about the field and whether the field is a
> primary/foreign key or not.
>
> And this is according to the schema.table.
>
> Thank you.
>
> >
> > View definition:
> >  SELECT current_database()::information_schema.sql_identifier AS
> > table_catalog,
> > nc.nspname::information_schema.sql_identifier AS table_schema,
> > c.relname::information_schema.sql_identifier AS table_name,
> > a.attname::information_schema.sql_identifier AS column_name,
> > a.attnum::information_schema.cardinal_number AS ordinal_position,
> > pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data
> AS
> > column_default,
> > CASE
> > WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull
> > THEN 'NO'::text
> > ELSE 'YES'::text
> > END::information_schema.yes_or_no AS is_nullable,
> > CASE
> > WHEN t.typtype = 'd'::"char" THEN
> > CASE
> > WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
> > 'ARRAY'::text
> > WHEN nbt.nspname = 'pg_catalog'::name THEN
> > format_type(t.typbasetype, NULL::integer)
> > ELSE 'USER-DEFINED'::text
> > END
> > ELSE
> > CASE
> > WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
> > 'ARRAY'::text
> > WHEN nt.nspname = 'pg_catalog'::name THEN
> > format_type(a.atttypid, NULL::integer)
> > ELSE 'USER-DEFINED'::text
> > END
> > END::information_schema.character_data AS data_type,
> > information_schema._pg_char_max_length(information_schema.
> _pg_truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.cardinal_numb
> > er AS character_maximum_length,
> > information_schema._pg_char_octet_length(information_
> schema._pg_truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.cardinal_nu
> > mber AS character_octet_length,
> > information_schema._pg_numeric_precision(information_
> schema._pg_truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.cardinal_nu
> > mber AS numeric_precision,
> > information_schema._pg_numeric_precision_radix(information_schema._pg_
> truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.
> cardi
> > nal_number AS numeric_precision_radix,
> > information_schema._pg_numeric_scale(information_
> schema._pg_truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.cardinal_number
> >  AS numeric_scale,
> > information_schema._pg_datetime_precision(information_schema._pg_
> truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.cardinal_n
> > umber AS datetime_precision,
> > information_schema._pg_interval_type(information_
> schema._pg_truetypid(a.*,
> > t.*), information_schema._pg_truetypmod(a.*,
> > t.*))::information_schema.character_data
> > AS interval_type,
> > NULL::integer::information_schema.cardinal_number AS
> interval_precision,
> > NULL::character varying::information_schema.sql_identifier AS
> > character_set_catalog,
> > NULL::character varying::information_schema.sql_identifier AS
> > character_set_schema,
> > NULL::character varying::information_schema.sql_identifier AS
> > character_set_name,
> > CASE
> > WHEN nco.nspname IS NOT NULL THEN current_database()
> > ELSE NULL::name
> > END::information_schema.sql_identifier AS collation_catalog,
> > nco.nspname::information_schema.sql_identifier AS collation_schema,
> > co.collname::information_schema.sql_identifier AS collation_name,
> > CASE
> > WHEN t.typtype = 'd'::"char" THEN current_database()
> > ELSE NULL::name
> > END::information_schema.sql_identifier AS domain_catalog,
> > CASE
> > WHEN t.typtype = 'd'::"char" THEN nt.nspname
> > ELSE NULL::name
> > END::information_schema.sql_identifier AS domain_schema,
> > CASE
> >