Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Patrick B]

Ok so guys

CREATE or REPLACE FUNCTION function_data_1()

RETURNS SETOF bigint AS $$


declare

row record;


BEGIN


[...]


FOR row IN EXECUTE '

SELECT

t1.file_id,

t1.path,

t1.account_id

FROM

table1 t1

JOIN

table3 t3 ON t3.file_Id = t1.file_id

WHERE

t3.migrated = 0

AND

*t3.account_id = 1112*

ORDER BY 1 LIMIT 30 '


[...]



 How can I make the function works with account_id?

*Example: select function_data_1(1112)*

and then it will do all the work just for that specific account_id?

If you guys please could give me the way to do that..
thanks
Patrick

Re: [GENERAL] [HACKERS] OUT parameter and RETURN table/setof

[Sridhar N Bamandlapally]

Existing application code written to call function in Oracle which return
no.of rows in out parameter and return-values is cursor-result

this need migrate to PostgreSQL, need help here

example: (actual function declaration only)
*Oracle:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR2,
v_rowsfound OUT INTEGER,
result_cursor1 OUT SYS_REFCURSOR
) ...


*PostgreSQL:*
*method 1*:
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER,
result_cursor1 OUT REFCURSOR
) ...

but this approach issue is, need to do in BEGIN - END block inside
with FETCH ALL IN ""
  - here we need/think common approach for database

*method 2:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER)
RETURNS TABLE/SETOF
...

this approach is not working


Thanks
Sridhar
OpenText







On Mon, Jun 6, 2016 at 5:57 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
>> Hi
>>
>> Is there any option in PGPLSQL which can RETURNS table or SETOF rows
>> along with an OUT parameter?
>>
>>
> ​No, there would be no point given the internals of how functions work.
>
> ​What is it you are trying to do?
>
> David J.
> ​
>
>

Re: [GENERAL] Postgres Dropped DB have recovered files how to restore

[John R Pierce]

On 6/6/2016 4:09 PM, Alvaro Herrera wrote:

I have no idea about Windows filesystems but you may be able to
"undelete" the files, as long as you don't touch the partition for
anything else; search the web for "undelete ntfs".  Once you undelete
you will need to put them back in the right places.  I suggest you get a
disk with twice as much space as the original; make an image copy of the
original disk and don't touch the original anymore.  Then try to
undelete the files from the image.  Use the list Adrian provided as a
guide for what you're missing.

You*may*  be able to recover the data, if you're lucky and careful.


that won't work if he did a DROP DATABASE as all the metadata is gone.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Re-sync slave server

[Patrick B]

My set up:


Master --> slave01 (streaming replication) --> slave02 (streaming
replication)
Master --> slave03 (wal_files 4 days old, not streaming replication)

The wal_files are stored into each server.
But when the slave03 was down, the wal_files weren't being copied into it.
We took too long to discover that, and now we lost some wal_files.. they've
been recycled...

So my question is:

Can I just do a RE-SYNC from slave01 to slave03 of the data folder?
Or do I have to sync all the database again?

I'm asking because the DB is 2.2TB, don't wanna do one step and discover
later that did not work and have to do all over again

Cheers
Patrick

Re: [GENERAL] Re-sync slave server

[Adrian Klaver]

On 06/06/2016 05:11 PM, Patrick B wrote:

Hi guys,

I'm using PostgreSQL 9.2

There is a backup server that is 4 days old replication by wal_files
(not streaming)

However, the server went down and I lost some wal_files.. and now the
replication is not working...


What is your set up?:

standby server
master --> | WAL archive --> standby_cluster


OR

 wal storage   standby server
master --> | WAL archive | --> standby

In other words where did you lose the WAL files?

More to the point are they still on the master or have they already been 
recycled?




Question:

Do I need to sync all the data folder from my master

Or just doing an incremental rsync would work?

Thanks
Patrick



--
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] Re-sync slave server

[Patrick B]

Hi guys,

I'm using PostgreSQL 9.2

There is a backup server that is 4 days old replication by wal_files (not
streaming)

However, the server went down and I lost some wal_files.. and now the
replication is not working...

Question:

Do I need to sync all the data folder from my master

Or just doing an incremental rsync would work?

Thanks
Patrick

Re: [GENERAL] Postgres Dropped DB have recovered files how to restore

[Alvaro Herrera]

lifetronics wrote:
> This morning I accidently deleted my database for my OpenERP accounting. I
> did not have a good backup system setup so I was unable to do a system
> restore. i did manage to recover the files the drop command removed but I
> dont know how to get the DB back into postgres? Can anyone help me with
> this. I realy need this db to be restored otherwise I am screwed.

I have no idea about Windows filesystems but you may be able to
"undelete" the files, as long as you don't touch the partition for
anything else; search the web for "undelete ntfs".  Once you undelete
you will need to put them back in the right places.  I suggest you get a
disk with twice as much space as the original; make an image copy of the
original disk and don't touch the original anymore.  Then try to
undelete the files from the image.  Use the list Adrian provided as a
guide for what you're missing.

You *may* be able to recover the data, if you're lucky and careful.

-- 
Álvaro Herrerahttp://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] Postgres Dropped DB have recovered files how to restore

[Dave Cramer]

Ok,

1) back up the file system now as it is. So you can start over when this
doesn't work.
2) create an empty database again with no data, but all of the DDL.
3) figure out the names of the new database files, and copy your old ones
in with the same name as the new ones

4) cross your fingers. I have no idea if this will work

5) pg_dump everything and reload it into a new db.

Let me know how it goes.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 6 June 2016 at 16:57, Adrian Klaver  wrote:

> On 06/04/2016 01:20 PM, lifetronics wrote:
>
>> This morning I accidently deleted my database for my OpenERP accounting. I
>> did not have a good backup system setup so I was unable to do a system
>> restore. i did manage to recover the files the drop command removed but I
>> dont know how to get the DB back into postgres? Can anyone help me with
>> this. I realy need this db to be restored otherwise I am screwed.
>>
>> These are the folders I recovered(i think my db was in the \base\19456
>> folder):
>>
>> C:\Program Files (x86)\OpenERP
>> 7.0-20141104-021038\PostgreSQL\data\base\19456\ *
>>
>> C:\Program Files (x86)\OpenERP
>> 7.0-20141104-021038\PostgreSQL\data\base\19456\ *
>>
>> C:\Program Files (x86)\OpenERP
>> 7.0-20141104-021038\PostgreSQL\data\base\311296
>>
>> C:\Program Files (x86)\OpenERP
>> 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ *
>>
>> C:\Program Files (x86)\OpenERP
>> 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ *
>>
>> I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much
>> apreciated I am kind of desperate.
>>
>
> This is what a complete $DATADIR for 9.3 looks like on a Linux source
> install:
>
> postgres@killi:/usr/local/pgsql93/data> l
> total 140
> drwx-- 16 postgres users  4096 Jan 11 09:06 ./
> drwxr-xr-x  7 root root   4096 Jun 27  2013 ../
> drwx-- 14 postgres users  4096 Jun  4  2015 base/
> drwx--  2 postgres users  4096 Nov 18  2015 global/
> drwx--  2 postgres users  4096 Jun 27  2013 pg_clog/
> -rw---  1 postgres users  4476 Jun 27  2013 pg_hba.conf
> -rw---  1 postgres users  1636 Jun 27  2013 pg_ident.conf
> drwxr-xr-x  3 postgres users 36864 Jan 11 08:42 pg_log/
> drwx--  4 postgres users  4096 Jun 27  2013 pg_multixact/
> drwx--  2 postgres users  4096 Jan 11 08:42 pg_notify/
> drwx--  2 postgres users  4096 Jun 27  2013 pg_serial/
> drwx--  2 postgres users  4096 Jun 27  2013 pg_snapshots/
> drwx--  2 postgres users  4096 Jan 11 09:06 pg_stat/
> drwx--  2 postgres users  4096 Jan 11 09:06 pg_stat_tmp/
> drwx--  2 postgres users  4096 Apr 26  2015 pg_subtrans/
> drwx--  2 postgres users  4096 Feb 23  2015 pg_tblspc/
> drwx--  2 postgres users  4096 Jun 27  2013 pg_twophase/
> -rw---  1 postgres users 4 Jun 27  2013 PG_VERSION
> drwx--  3 postgres users  4096 Jun  4  2015 pg_xlog/
> -rw---  1 postgres users 20329 Jan 11 09:06 postgresql.conf
> -rw---  1 postgres users63 Jan 11 08:42 postmaster.opts
>
>
> So if what you show is everything then you are short some important
> directories.
>
>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> 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] Postgres Dropped DB have recovered files how to restore

[Adrian Klaver]

On 06/04/2016 01:20 PM, lifetronics wrote:

This morning I accidently deleted my database for my OpenERP accounting. I
did not have a good backup system setup so I was unable to do a system
restore. i did manage to recover the files the drop command removed but I
dont know how to get the DB back into postgres? Can anyone help me with
this. I realy need this db to be restored otherwise I am screwed.

These are the folders I recovered(i think my db was in the \base\19456
folder):

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\311296

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ *

I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much
apreciated I am kind of desperate.


This is what a complete $DATADIR for 9.3 looks like on a Linux source 
install:


postgres@killi:/usr/local/pgsql93/data> l
total 140
drwx-- 16 postgres users  4096 Jan 11 09:06 ./
drwxr-xr-x  7 root root   4096 Jun 27  2013 ../
drwx-- 14 postgres users  4096 Jun  4  2015 base/
drwx--  2 postgres users  4096 Nov 18  2015 global/
drwx--  2 postgres users  4096 Jun 27  2013 pg_clog/
-rw---  1 postgres users  4476 Jun 27  2013 pg_hba.conf
-rw---  1 postgres users  1636 Jun 27  2013 pg_ident.conf
drwxr-xr-x  3 postgres users 36864 Jan 11 08:42 pg_log/
drwx--  4 postgres users  4096 Jun 27  2013 pg_multixact/
drwx--  2 postgres users  4096 Jan 11 08:42 pg_notify/
drwx--  2 postgres users  4096 Jun 27  2013 pg_serial/
drwx--  2 postgres users  4096 Jun 27  2013 pg_snapshots/
drwx--  2 postgres users  4096 Jan 11 09:06 pg_stat/
drwx--  2 postgres users  4096 Jan 11 09:06 pg_stat_tmp/
drwx--  2 postgres users  4096 Apr 26  2015 pg_subtrans/
drwx--  2 postgres users  4096 Feb 23  2015 pg_tblspc/
drwx--  2 postgres users  4096 Jun 27  2013 pg_twophase/
-rw---  1 postgres users 4 Jun 27  2013 PG_VERSION
drwx--  3 postgres users  4096 Jun  4  2015 pg_xlog/
-rw---  1 postgres users 20329 Jan 11 09:06 postgresql.conf
-rw---  1 postgres users63 Jan 11 08:42 postmaster.opts


So if what you show is everything then you are short some important 
directories.







--
View this message in context: 
http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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] Why threads every 30 seconds?

[Tom Lane]

Bryan Henderson  writes:
> Looking at audit logs, I see that my Postgresql server generates a new thread
> precisely every 30 seconds, in two series (so 4 threads every minute).  This
> is an otherwise idle server.

> Does anyone know what these threads are for?  Just curious.

Autovacuum, likely.  The rate would depend on your autovacuum_naptime
and how many active databases you have.

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] mobile apps: async repl/sync

[Wim Bertels]

Hallo,

some loose thoughts on mobile app development.

* Users want mobile apps to be quick and snappy as they call it. So if
the app has to do (a lot of) queries to a remote datasource, it won't be
fast and snappy.
* For a while now json is a popular choice with programmers (of mobile apps, 
who will use
for example json content with javascript), but i'm not convinced it will
stay this popular in the future to come; it all depends on how well the 
programmer understands data.
The reason: quick simply, json is an hierarchical structure with all the 
shortcomings that any
hierarchical structure has as a datastore. (cf XML in the 90ties)
Anyway the json datatype is there, a nice addition, giving these options to the 
developer.

The relational model is likely to continue, mixed and extended with
other forms of organising your data (as it many RDBMS already are). FDWs
are the prime example of this. 

What if you want to store your data in a relational model for a mobile
app that's snappy and fast?
* U can't run postgres on the mobile device
* U can use some lightweight relational database like sqlite.
So logically if you want to combine them, having a remote database (eg
postgres) and a local database(eg sqlite) -similar to the way
json/mongodb or couch is used now- would be good for the future. So an
easy async (partial) replication/synchronisation between for example
sqlite and postgres would be very interesting for people who want to
(quickly?) develop snappy mobile apps (often in need of a local and
remote datastore) that benefit from the relational model.

Many app developers don't really care about the data,
they just want to make an app, so they ofen look at frameworks or stacks.
Remember LAMP, i think this was a big reason for the succes of mysql.
Now the same is happening with mongo, eg https://meanjs.org/ and others.
https://thenapstack.wordpress.com/ is nice, 
but doesn't really offer a documented solution for those developers.

I probably missed some techs out there; 
but this all being said, i would like to thank the community for their work.

-- 
mvg,
Wim Bertels

--
ROMEO:  Courage, man; the hurt cannot be much.
MERCUTIO:   No, 'tis not so deep as a well, nor so wide
as a church-door; but 'tis enough, 'twill serve.





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

[GENERAL] Why threads every 30 seconds?

[Bryan Henderson]

Looking at audit logs, I see that my Postgresql server generates a new thread
precisely every 30 seconds, in two series (so 4 threads every minute).  This
is an otherwise idle server.

Does anyone know what these threads are for?  Just curious.

-- 
Bryan Henderson   San Jose, California


-- 
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 Dropped DB have recovered files how to restore

[lifetronics]

This morning I accidently deleted my database for my OpenERP accounting. I
did not have a good backup system setup so I was unable to do a system
restore. i did manage to recover the files the drop command removed but I
dont know how to get the DB back into postgres? Can anyone help me with
this. I realy need this db to be restored otherwise I am screwed.

These are the folders I recovered(i think my db was in the \base\19456
folder):

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\311296

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ *

C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ *

I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much
apreciated I am kind of desperate.




--
View this message in context: 
http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] postgres_fdw and Kerberos authentication

[Stephen Frost]

Jean-Marc,

* Jean-Marc Lessard (jean-marc.less...@ultra-ft.com) wrote:
> Stephen Frost [sfr...@snowman.net]  wrote:
> > The database owner operating system user has to be trusted, along with any 
> > superusers in the database, but if you assume those, then having PG manage 
> > the different Kerberos cache files
> > (one for each backend which has authenticated via Kerberos and passed 
> > through delegation credentials) should work.
> > Clearly, we can't give the user control over which credential cache to use.
> 
> True, in such a case (single sign on) the user should not specify a user in 
> the user mapping, so that its own Kerberos ticket be used to authenticate.

I don't know that it's actually an issue for the user to specify the
mapping- either it'll be allowed or not, based on the credentials in the
Kerberos cache and pg_ident mappings.  What we can't do is allow the
user to control which cache they are able to use.

In other words, there should be one credential cache per backend process
and that holds exactly the credentials which are forwarded from the
client system.

> > Having to trust the OS user and superusers with those credentials isn't any 
> > different from using passwords with postgres_fdw.
> 
> OS user and superusers, should not have access and allowed to manage the 
> credential files.

This isn't possible with traditional Unix permissions.  Perhaps
something could be done with SELinux, but we're not going to depend on
that.

Ultimately, the credential cache must be available to the backend
process, which runs as the OS user.  The PG superuser can execute
arbitrary commands as the OS user, so there isn't any distinction
between the OS user and the PG superuser.

As mentioned up-thread, this is exactly the same as Apache, except that
Apache happens to run as root whereas we run as a non-root user.

> For example, in a secure environment with separation of duties at the 
> organization level (tier1, tier3, superuser, sys admins, etc), the tier1 DB 
> users cannot connect onto the DB server (as OS user), but may move data form 
> one database to another.

Sure, I assumed that we were discussing a case where DB users connect to
the database, not log on to the DB server as an OS user.

> I agree that tier1 users cannot query the catalog and see other user 
> password, but a superuser can, which is considered a security breach by 
> auditors.
> Storing a password in plain text even for a short period of time is 
> unfortunately not authorized.

Agreed.  This isn't the same as a Kerberos credential cache, but it's
not as far different as one might assume either.  The superuser will be
able to access the credential cache of anyone who has forwarded their
Kerberos ticket to the server, which is the same for any environment
that allows Kerberos credential proxying.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Whither recovery.conf?

[Richard Tisch]

On Mon, Jun 6, 2016 at 10:12 PM, Vik Fearing  wrote:
> On 06/06/16 15:07, Vik Fearing wrote:
>> It seems the commitfest link in there doesn't work anymore.  I should
>> probably bring that up in a separate thread.
>
> It's in the old commitfest app.  Here's a new link for it:
> https://commitfest-old.postgresql.org/action/patch_view?id=1293

Thanks!

Richard.


-- 
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] Whither recovery.conf?

[Vik Fearing]

On 06/06/16 15:07, Vik Fearing wrote:
> It seems the commitfest link in there doesn't work anymore.  I should
> probably bring that up in a separate thread.

It's in the old commitfest app.  Here's a new link for it:
https://commitfest-old.postgresql.org/action/patch_view?id=1293
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] Whither recovery.conf?

[Vik Fearing]

On 06/06/16 14:50, Richard Tisch wrote:
> Hi there,
> 
> I was just wondering about the statement below in another thread:
> 
> 2016-06-04 22:58 GMT+09:00 Vik Fearing :
>> There are plans to allow SQL
>> access to the parameters in recovery.conf (or to merge them into
>> postgresql.conf) but that's not currently possible.
> 
> Are there any active plans or patches on this front? I did a bit of searching,
> I see one thread from 2013 (this one:
> https://www.postgresql.org/message-id/CAJKUy5id1eyweK0W4+yyCM6+-qYs9erLidUmb=1a-qybgtw...@mail.gmail.com),
> but it's not clear to where things are at the
> moment. Mind you I might be searching in the wrong places and/or for the
> wrong terms, it's happened before so apologies if I'm missing the obvious.

The last I saw of it was in 2014 (wow, time flies).

https://www.postgresql.org/message-id/flat/546D2316.2000206%40agliodbs.com

It seems the commitfest link in there doesn't work anymore.  I should
probably bring that up in a separate thread.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] WAL's listing in pg_xlog by some sql query

[Stephen Frost]

* Vik Fearing (v...@2ndquadrant.fr) wrote:
> On 03/06/16 04:32, Michael Paquier wrote:
> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar  
> > wrote:
> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost  wrote:
> >>> Given the usefulness of this specific query and that it could be used
> >>> without risk of the user being able to gain superuser access through it,
> >>> I'd like to see a new function added which does not have the superuser
> >>> check, but is not allowed to be called by public initially either.
> 
> CREATE FUNCTION ls_dir(text)
>  RETURNS SETOF text
>  LANGUAGE sql
>  SECURITY DEFINER
> AS 'select * from pg_ls_dir($1)';

This isn't a good idea as it allows access to a great deal more than
just the number of xlogs.  Further, as described above, it gives that
access to everyone and not just to specific roles.

This is a great example of why we should provide an explicit function
which is documented (both in our documentation and in the documentation
of tools like check_postgres.pl) that users can use and can GRANT access
to for their monitoring systems which gives access to only the
information needed- that is, the number of xlog segments.

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] Whither recovery.conf?

[Richard Tisch]

Hi there,

I was just wondering about the statement below in another thread:

2016-06-04 22:58 GMT+09:00 Vik Fearing :
> There are plans to allow SQL
> access to the parameters in recovery.conf (or to merge them into
> postgresql.conf) but that's not currently possible.

Are there any active plans or patches on this front? I did a bit of searching,
I see one thread from 2013 (this one:
https://www.postgresql.org/message-id/CAJKUy5id1eyweK0W4+yyCM6+-qYs9erLidUmb=1a-qybgtw...@mail.gmail.com),
but it's not clear to where things are at the
moment. Mind you I might be searching in the wrong places and/or for the
wrong terms, it's happened before so apologies if I'm missing the obvious.

Thanks!

Richard.


-- 
Those who do not understand SQL are condemned to reinvent it, poorly


-- 
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] [HACKERS] OUT parameter and RETURN table/setof

[David G. Johnston]

On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:

> Hi
>
> Is there any option in PGPLSQL which can RETURNS table or SETOF rows along
> with an OUT parameter?
>
>
​No, there would be no point given the internals of how functions work.

​What is it you are trying to do?

David J.
​

[GENERAL] OUT parameter and RETURN table/setof

[Sridhar N Bamandlapally]

Hi

Is there any option in PGPLSQL which can RETURNS table or SETOF rows along
with an OUT parameter?

please

Thanks
Sridhar
OpenText

Re: [GENERAL] ALTER TABLE and vacuum

[Vik Fearing]

On 06/06/16 10:55, Rick Widmer wrote:
> Do I need to vacuum after an alter table command?

If the specific command you do rewrites the table, you absolutely should
vacuum in order to restore the visibility map.  Otherwise, it not necessary.

> Does it matter if there is a default or if we accept NULLs for the new
> field?

Yes, that makes a difference.  If you add a column that defaults to
NULL, the table will not be rewritten.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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

[GENERAL] ALTER TABLE and vacuum

[Rick Widmer]

Do I need to vacuum after an alter table command?

Does it matter if there is a default or if we accept NULLs for the new 
field?



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

[Bertrand Paquet]

Hi,

Thx you for answering.

Regards,

Bertrand

2016-06-06 10:22 GMT+02:00 Vik Fearing :

> On 06/06/16 09:54, Masahiko Sawada wrote:
> > On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
> >> On 02/06/16 15:32, Bertrand Paquet wrote:
> >>> Hi,
> >>>
> >>> On an hot standby streaming server, is there any way to know, in SQL,
> to
> >>> know the ip of current master ?
> >>
> >> No.
> >>
> >>> The solution I have is to read the recovery.conf file to find
> >>> primary_conninfo,
> >>
> >> That is currently the only solution.  There are plans to allow SQL
> >> access to the parameters in recovery.conf (or to merge them into
> >> postgresql.conf) but that's not currently possible.
> >
> > It might not be a right way but how about using pg_read_file()?
> > postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
> > '.*primary_conninfo = (.*)', '\1');
> >   regexp_replace
> > ---
> >  'host=localhost port=5550 application_name=node1'+
> >
> > (1 row)
> >
> > You can get the master server information via SQL from standby server.
>
> This is a good idea, but suffers the same problem that Bertrand has with
> looking at the file a different way: if the file was changed but the
> standby server has not been restarted, it's (potentially) not going to
> be the correct information.
> --
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>

Re: [GENERAL] Replication

[Vik Fearing]

On 06/06/16 09:54, Masahiko Sawada wrote:
> On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
>> On 02/06/16 15:32, Bertrand Paquet wrote:
>>> Hi,
>>>
>>> On an hot standby streaming server, is there any way to know, in SQL, to
>>> know the ip of current master ?
>>
>> No.
>>
>>> The solution I have is to read the recovery.conf file to find
>>> primary_conninfo,
>>
>> That is currently the only solution.  There are plans to allow SQL
>> access to the parameters in recovery.conf (or to merge them into
>> postgresql.conf) but that's not currently possible.
> 
> It might not be a right way but how about using pg_read_file()?
> postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
> '.*primary_conninfo = (.*)', '\1');
>   regexp_replace
> ---
>  'host=localhost port=5550 application_name=node1'+
> 
> (1 row)
> 
> You can get the master server information via SQL from standby server.

This is a good idea, but suffers the same problem that Bertrand has with
looking at the file a different way: if the file was changed but the
standby server has not been restarted, it's (potentially) not going to
be the correct information.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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

[Masahiko Sawada]

On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
> On 02/06/16 15:32, Bertrand Paquet wrote:
>> Hi,
>>
>> On an hot standby streaming server, is there any way to know, in SQL, to
>> know the ip of current master ?
>
> No.
>
>> The solution I have is to read the recovery.conf file to find
>> primary_conninfo,
>
> That is currently the only solution.  There are plans to allow SQL
> access to the parameters in recovery.conf (or to merge them into
> postgresql.conf) but that's not currently possible.
>

It might not be a right way but how about using pg_read_file()?
postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
'.*primary_conninfo = (.*)', '\1');
  regexp_replace
---
 'host=localhost port=5550 application_name=node1'+

(1 row)

You can get the master server information via SQL from standby server.

Regards,

--
Masahiko Sawada


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