[GENERAL] Very high latency, low bandwidth replication

[Bob Jolliffe]

Hi

I have been grappling with a problem for some time I would appreciate some
advice on.  We have a public health application which is web based with a
postgresql backing store which is designed for use by the public sector
ministry of health in a significant number of African, Asian and other
countries (http//:dhis2.org).  "Traditionally" it is hosted as a national
data warehouse application with users dispersed amongst district offices
and sometimes health facilities around the country.

Particularly in many countries in Africa the public sector typically has
limited data centre infrastructure to reliably host the application
in-house and so a good number have opted to use some global cloud service
(infrastructure as a service) to ensure maximum availability of the
application.  Others have managed to make use of in-country resources such
as national ISPs and mobile companies.  There are many cost-benefit and
governance considerations behind these decisions which I don't need to go
into here.

Whereas ministries have been prepared to do this there are important to
reasons to ensure that a backup of the database can be maintained in the
ministry.  So we attempt to grab the nightly snapshot backups from the
database each night.  In the past I have attempted this somewhat
simplistically with rsync over ssh but it is a very inefficient approach
and particularly so over weak internet connections.

What are people's thoughts about a more optimal solution?  I would like to
use a more incremental approach to replication.  This does not have to be a
"live" replication .. asynchronously triggering once every 24 hours is
sufficient.  Also there are only a subset of tables which are required (the
rest consist of data which is generated).

Appreciate any advice.

Regards
Bob

Re: [GENERAL] [ADMIN] [pgadmin-support] Best backup strategy for production systems

[Oliver]

Ok, thanks.
I mean by original wal segments to files generated in pg_xlog, yes. I have
to 0 the parameter wal_keep_segments. They are being generated with 16MB of
size and they are being rotated. What parameter controls how many files can
be generated? Or will it grow indefinitely?
Thanks beforehand.

Cheers...


2014-06-27 15:36 GMT+01:00 :

> Oliver,
>
> You want to retain all the segments archived between backups. That's the
> safest method that I know of. What do you mean by original WAL segments?
> Are you speaking about what PostgreSQL writes into pg_xlog? That's
> controlled by the wal_keep_segments parameter in the Postgresql.conf file.
>
> Sent from my iPad
>
> On Jun 27, 2014, at 8:55 AM, Oliver  wrote:
>
> Thank you very much for your reply.
> I've spoken with my boss, databases aren't so important, so if there is a
> little of data lost, there isn't problem .. so I'm configuring this with
> continuous archiving and base backups. If you are expert, please, I would
> like make you some questions ..
> I only have a server (there isn't slave server). I have doing continuous
> archiving to another filesystem (of other physical disks), I think that it
> is working well .. One question is, original wal files are keeped how many
> time? 2 days? I only have control archived wal files, about how many time I
> want keep them, isn't it?
> When I do a base backup, is it normal that it doesn't copy pg_xlog data,
> isn't? Because it is doing continuous archiving :-?
> The command that I'm using is (I would like that it copies all
> tablespaces, but I'm not sure if it is doing it):
>
> pg_basebackup -D 27062014 -Ft -z -Z 5 -l "base_backup_27062014" -P
>
> Output:
>
> 20290/20290 kB (100%), 1/1 tablespace
> NOTICE:  pg_stop_backup completado, todos los segmentos de WAL requeridos
> han sido archivados
>
> I have now only default databases (postgres and templates), is it normal
> that it puts only 1/1 tablespace? Why it says that all required wal segmens
> have been archived if continuous archiving is enabled? I'm seeing now that
> it has copied some files and there is one file named .backup in the
> archived wal filesystem.
> Thanks beforehand.
>
> Cheers...
>
>
> 2014-06-25 14:49 GMT+01:00 Andreas :
>
>> Hi
>> you might also set up a slave server that replicates all write commands
>> as hot standby.
>> It's pretty easy to do that and you won't lose any data if your
>> productive server goes up in flames.
>> It won't slow down your system.
>>
>> In case you don't want to run another PC as the slave server you can have
>> it as a virtual machine on your workstation. Obviously you shouldn't run it
>> as guest on the db machine.   ;)
>> I had a VBox do this for some time. It had only 1GB virtual RAM.
>>
>> I do hourly full db-dumps as a cron job just to be sure.   :-}
>>
>> Every other strategy like regular dumps or automatic log shipping has a
>> certain amount of time where all modifications to the db would be lost in
>> case something happens to your server.
>>
>> PG supports automatic log shipping but the log segments are 16MB big.
>> Depending on how much write access your db gets it can take a while before
>> the segment is full and gets shipped out.
>>
>> You might also consider to use a RAID 1 to store the data directoy on.
>> In my case there are 3 drives. One for the OS and 2 as a software RAID.
>> Again that is easy to set up and saves the db in case the harddrive dies.
>> This even speeds up read access to the db a bit.
>>
>>
>>
>> Am 17.06.2014 11:51, schrieb Oliver:
>>
>>  Hi,
>>> I'm a newbie in postgresql. I've mounted my first postgresql instance,
>>> it is empty now, only with default postgres DB.
>>> It is under Linux, with 2 filesystems, one for data and another for
>>> archiving (I've enabled archiving as it will be for production).
>>> Could someone recommend me a strategy for backups, scripts and so on?
>>> Can base backup be done with the system up (postgres up), isn't it?
>>> Would it be ok if I do a base backup each week and archiving backup each
>>> day?
>>> As I've not configured backups (and archiving deletion), I've had my
>>> first problem and it is that my archiving filesystem (FS) is full and
>>> archiver process is showing "failed" with the last wal file copy (normal as
>>> archiving FS is full).
>>> Please, recommend me what I should make now .. I should create another
>>> network FS for base backups and archiving backups? When I have my first
>>> base backup, could I then delete archiving files, isn't it?
>>> My archiving FS has 20GB, I don't understand as with a system without
>>> load (it will be for production, but it hasn't databases now .. only
>>> postgres), how it full the FS in a few days ... Is it normal?
>>> Thanks beforehand.
>>>
>>> Cheers...
>>>
>>
>>
>

Re: [GENERAL] [pgadmin-support] Best backup strategy for production systems

[Oliver]

Thank you very much for your replies.

Cheers...


2014-06-29 13:28 GMT+01:00 Michael Paquier :

>
>
>
> On Fri, Jun 27, 2014 at 9:55 PM, Oliver  wrote:
>
>> Thank you very much for your reply.
>> I've spoken with my boss, databases aren't so important, so if there is a
>> little of data lost, there isn't problem .. so I'm configuring this with
>> continuous archiving and base backups. If you are expert, please, I would
>> like make you some questions ..
>> I only have a server (there isn't slave server). I have doing continuous
>> archiving to another filesystem (of other physical disks), I think that it
>> is working well .. One question is, original wal files are keeped how many
>> time?
>>
> 2 days? I only have control archived wal files, about how many time I want
>> keep them, isn't it?
>>
>
>
>
>> When I do a base backup, is it normal that it doesn't copy pg_xlog data,
>> isn't? Because it is doing continuous archiving :-?
>>
> A base backup can be used as-is with the archived WAL files that it can
> use for recovery.
>
>
>> The command that I'm using is (I would like that it copies all
>> tablespaces, but I'm not sure if it is doing it):
>> pg_basebackup -D 27062014 -Ft -z -Z 5 -l "base_backup_27062014" -P
>>
> A base backup taken in tar format creates a set of tarballs: one for the
> base (base tablespace with configuration file, etc.) and one for each
> tablespace named with the OID of the tablespace. It is actually better to
> use a tar format as plain format will try to link to existing folders when
> taking a base backup on the same server as the node it is connecting to if
> this server has extra tablespaces.
>
>
>> Output:
>>
>> 20290/20290 kB (100%), 1/1 tablespace
>> NOTICE:  pg_stop_backup completado, todos los segmentos de WAL requeridos
>> han sido archivados
>>
>> I have now only default databases (postgres and templates), is it normal
>> that it puts only 1/1 tablespace?
>>
> Yes.
>
>
>> Why it says that all required wal segmens have been archived if
>> continuous archiving is enabled?
>>
> It is necessary to perform a WAL file switch when calling pg_stop_backup
> to ensure that all the necessary WAL files for the backup have been
> archived.
> --
> Michael
>

[GENERAL] collecting employees who completed 5 and 10 years in the current month

[Arup Rakshit]

I have employee table. Where I have a column joining_date. Now I am looking for 
a way to get all employee, who completed 5 years, 10 years current month. How 
to do so ? I am not able to figure this out.
 
Regards,
Arup Rakshit

Re: [GENERAL] collecting employees who completed 5 and 10 years in the current month

[Szymon Guz]

On 30 June 2014 12:38, Arup Rakshit  wrote:

> I have employee table. Where I have a column joining_date. Now I am
> looking for a way to get all employee, who completed 5 years, 10 years
> current month. How to do so ? I am not able to figure this out.
>
> Regards,
> Arup Rakshit
>

Hi,
take a look at this example:

I've created a sample table:

create table users(id serial, joining_date date);

and filled it with sample data:

insert into users(joining_date) select now() - (j::text || 'days'
)::interval from generate_series(1,1) j;

Then the query showing up all users who complete 5 and 10 years this month
can look like:

with u as (
  select id, date_trunc('month', age(now()::date, joining_date)) age
  from users
)
select *
from u
where u.age in ('5 years', '10 years');


- Szymon

Re: [GENERAL] [ADMIN] [pgadmin-support] Best backup strategy for production systems

[Michael Paquier]

Please avoid top-posting...

On Mon, Jun 30, 2014 at 7:24 PM, Oliver  wrote:

> Ok, thanks.
> I mean by original wal segments to files generated in pg_xlog, yes. I have
> to 0 the parameter wal_keep_segments. They are being generated with 16MB of
> size and they are being rotated. What parameter controls how many files can
> be generated? Or will it grow indefinitely?
>
checkpoint_segments. A maximum of (3 * checkpoint_segments + 1) WAL
segments can generated. This can be more though as this is a soft limit,
but once this limit is reached server will remove WAL segments instead of
keeping them. Per the docs:
http://www.postgresql.org/docs/9.3/static/wal-configuration.html
-- 
Michael

Re: [GENERAL] Very high latency, low bandwidth replication

[Francisco Olarte]

Hi Bob.

On Mon, Jun 30, 2014 at 10:05 AM, Bob Jolliffe  wrote:
> What are people's thoughts about a more optimal solution?  I would like to
> use a more incremental approach to replication.  This does not have to be a
> "live" replication .. asynchronously triggering once every 24 hours is
> sufficient.  Also there are only a subset of tables which are required (the
> rest consist of data which is generated).


If you only need to replicate once every 24 hours, which means you can
tolerate lags, you could try log shipping. Instead of sending the wal
records from master to standby directly just spool them, compress them
as much as you can ( I would try pglesslog plus an XZ on it's output
), and send it once a day. This for the 'incremental part'. For the
only a subset of tables, you could try to set up a local partial
mirror using any of the trigger based replication products and then do
log-shipping of that.

Also, the logical replication slot stuff added to the latest version
seems really promissing for this kind of thing, but I'm not familiar
enough with it to recommend anything.

Also, depending on your data updating patterns, database sizes and
other stuff, a trigger based replication approach can save a lot of
traffic. I mean, if you have records which are heavily updated, but
only replicate once a day, you can collapse all the day stuff in a
single update. I once did a similar thing to transmit deltas over a
2400bps modem by making daily sorted dumps and sending daily deltas
with previous day ( it needed a bit of coding, about a couple hundred
lines, but produced ridiculously small deltas, and with a bit of care
their application was idempotent, which simplified the recovery on
errors ).

   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] select users as per their years completion date current year

[Arup Rakshit]

Suppose I have users table. I have a joining_date. Now I only want those users 
who completed 5 years, 10 years, and in which date they completed. 

Example:

Out of all users, suppose below users only -

Foo_1 on 24/1/2014 completed 10 years
Foo_2 on 2/2/2014 completed  10 years
Foo_3 on 14/3/2014 completed 15 years
Foo_4 on 21/3/2014 completed 5 years

They should come in the output 
 
Regards,
Arup Rakshit

Re: [GENERAL] Examples of projects that use Postgres "as API server"

[Emre Hasegeli]

> I've been using Postgres for a small project and I've been very impressed
> by its flexibility in defining new types and functions.  I very much like
> having the ability to define a clean relational model and then a set of
> functions that act as the API to the data stored in my model.
> 
> Does anyone know of a project or projects that use Postgres in a fashion
> like this?  I'd love to read the schemas of a project like that.

I am developing an application to send bulk emails.  Most of its logic
is inside PostgreSQL.  The schema is here:

http://github.com/tart/tart-mailer/tree/master/db


-- 
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] Examples of projects that use Postgres "as API server"

[Bill Moran]

On Mon, 30 Jun 2014 16:46:42 +0300 Emre Hasegeli  wrote:

> > I've been using Postgres for a small project and I've been very impressed
> > by its flexibility in defining new types and functions.  I very much like
> > having the ability to define a clean relational model and then a set of
> > functions that act as the API to the data stored in my model.
> > 
> > Does anyone know of a project or projects that use Postgres in a fashion
> > like this?  I'd love to read the schemas of a project like that.

I'd forgotten about this, and for some reason remembered it now:
https://schemaverse.com/


-- 
Bill Moran 


-- 
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] Examples of projects that use Postgres "as API server"

[Thomas Kellerer]

Ben Ellis, 29.06.2014 03:10:
> Hi all,
> 
> I've been using Postgres for a small project and I've been very
> impressed by its flexibility in defining new types and functions.  I
> very much like having the ability to define a clean relational model
> and then a set of functions that act as the API to the data stored in
> my model.
> 
> Does anyone know of a project or projects that use Postgres in a
> fashion like this?  I'd love to read the schemas of a project like
> that.
> 
> It's entirely possible that I'm just nuts, but it seems like a very
> good idea to me-- or at least a happy medium between 'the database is
> just tables' and 'cram everything into the database!'
> 

LedgerSMB comes to mind:

http://ledgersmbdev.blogspot.de/2012/01/thoughts-on-what-to-put-in-database.html





-- 
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] collecting employees who completed 5 and 10 years in the current month

[Rebecca Clarke]

Hi Arup,

Two ways come to mind for me. They're pretty much the same as Szymon's,
just minus the sample table creation. I would suggest creating a view
instead, so you can just select from it whenever you please.


 create view vw_employee as
   select * from employees
   where ((age(joining_date::date) like '5 years%') or
(age(joining_date::date) like '10 years%') )

or

 create view vw_employee as
   select * from employees
   where
  ((to_char(joining_date, '-MM') = to_char((now() - interval '5
years'), '-MM') )
   or
  (to_char(joining_date, '-MM') = to_char((now() - interval '10
years'), '-MM')))



And then to check the employees who have completed 5 or 10 years, you'll
just do:

   select * from vw_employee


This is done off the top of my head so there will likely be syntax errors,
but I hope this can give you a general idea.


 - Rebecca


On Mon, Jun 30, 2014 at 12:00 PM, Szymon Guz  wrote:

>
> On 30 June 2014 12:38, Arup Rakshit  wrote:
>
>> I have employee table. Where I have a column joining_date. Now I am
>> looking for a way to get all employee, who completed 5 years, 10 years
>> current month. How to do so ? I am not able to figure this out.
>>
>> Regards,
>> Arup Rakshit
>>
>
> Hi,
> take a look at this example:
>
> I've created a sample table:
>
> create table users(id serial, joining_date date);
>
> and filled it with sample data:
>
> insert into users(joining_date) select now() - (j::text || 'days'
> )::interval from generate_series(1,1) j;
>
> Then the query showing up all users who complete 5 and 10 years this month
> can look like:
>
> with u as (
>   select id, date_trunc('month', age(now()::date, joining_date)) age
>   from users
> )
> select *
> from u
> where u.age in ('5 years', '10 years');
>
>
> - Szymon
>

Re: [GENERAL] collecting employees who completed 5 and 10 years in the current month

[Arup Rakshit]

On Monday, June 30, 2014 04:52:32 PM you wrote:
> Hi Arup,
> 
> Two ways come to mind for me. They're pretty much the same as Szymon's,
> just minus the sample table creation. I would suggest creating a view
> instead, so you can just select from it whenever you please.
> 
> 
>  create view vw_employee as
>select * from employees
>where ((age(joining_date::date) like '5 years%') or
> (age(joining_date::date) like '10 years%') )
> 

But I am using Ruby on Rails framework to develop web application. Here I use 
basically query. If no way, then I go for view. It seems I can use this as a 
select query. But view of course a good idea.In our web app, we will show this 
data as a report. A user can run it whenever he/she feel. All query seems like 
current day query. But I really need current month. Again it sometimes feel 
like ok, sometimes not.  :-)

-- 

Regards,
Arup Rakshit

Debugging is twice as hard as writing the code in the first place. Therefore, 
if you write the code as cleverly as possible, you are, by definition, not 
smart enough to debug it.

--Brian Kernighan


-- 
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] collecting employees who completed 5 and 10 years in the current month

[Szymon Guz]

On 30 June 2014 17:52, Rebecca Clarke  wrote:

> Hi Arup,
>
> Two ways come to mind for me. They're pretty much the same as Szymon's,
> just minus the sample table creation. I would suggest creating a view
> instead, so you can just select from it whenever you please.
>
>
>  create view vw_employee as
>select * from employees
>where ((age(joining_date::date) like '5 years%') or
> (age(joining_date::date) like '10 years%') )
>
> or
>
>  create view vw_employee as
>select * from employees
>where
>   ((to_char(joining_date, '-MM') = to_char((now() - interval '5
> years'), '-MM') )
>or
>   (to_char(joining_date, '-MM') = to_char((now() - interval '10
> years'), '-MM')))
>
>
>
> And then to check the employees who have completed 5 or 10 years, you'll
> just do:
>
>select * from vw_employee
>
>
> This is done off the top of my head so there will likely be syntax errors,
> but I hope this can give you a general idea.
>
>
>  - Rebecca
>
>
> On Mon, Jun 30, 2014 at 12:00 PM, Szymon Guz  wrote:
>
>>
>> On 30 June 2014 12:38, Arup Rakshit  wrote:
>>
>>> I have employee table. Where I have a column joining_date. Now I am
>>> looking for a way to get all employee, who completed 5 years, 10 years
>>> current month. How to do so ? I am not able to figure this out.
>>>
>>> Regards,
>>> Arup Rakshit
>>>
>>
>> Hi,
>> take a look at this example:
>>
>> I've created a sample table:
>>
>> create table users(id serial, joining_date date);
>>
>> and filled it with sample data:
>>
>> insert into users(joining_date) select now() - (j::text || 'days'
>> )::interval from generate_series(1,1) j;
>>
>> Then the query showing up all users who complete 5 and 10 years this
>> month can look like:
>>
>> with u as (
>>   select id, date_trunc('month', age(now()::date, joining_date)) age
>>   from users
>> )
>> select *
>> from u
>> where u.age in ('5 years', '10 years');
>>
>>
>> - Szymon
>>
>
>
Yea, quite nice Rebecca, I always forget the simplest solutions :)

- Szymon

[GENERAL] Re: collecting employees who completed 5 and 10 years in the current month

[David G Johnston]

Rebecca Clarke-2 wrote
>  create view vw_employee as
>select * from employees
>where ((age(joining_date::date) like '5 years%') or
> (age(joining_date::date) like '10 years%') )

This does not give the correct answer to the poster's question - the LIKE
with a trailing "%" will pick up non-round intervals.


>  create view vw_employee as
>select * from employees
>where
>   ((to_char(joining_date, '-MM') = to_char((now() - interval '5
> years'), '-MM') )
>or
>   (to_char(joining_date, '-MM') = to_char((now() - interval '10
> years'), '-MM')))

This works - find out what year-month it was x years ago and compare it to
the corresponding year-month of the requested date.

If one were to be doing this often it would probably be worth while to
either use a functional index or a trigger-maintained field to store the
"to_char(joining_date)" calculation.

WHERE joining_date_yearmonth = ANY( ARRAY['2009-06','1999-06']::text[] );

Was also pondering using a VARIADIC function to pass in integer year(s),
which would then be converted into the corresponding array.

Haven't actually played with the above and so not sure how index-friendly
the =ANY(...) construct is but it does allow you to avoid add entire OR
clauses and instead simply supply a different comparison array.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/collecting-employees-who-completed-5-and-10-years-in-the-current-month-tp5809762p5809828.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] collecting employees who completed 5 and 10 years in the current month

[Arup Rakshit]

On Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote:
> Hi Arup,
> 
> Two ways come to mind for me. They're pretty much the same as Szymon's,
> just minus the sample table creation. I would suggest creating a view
> instead, so you can just select from it whenever you please.
> 
> 
>  create view vw_employee as
>select * from employees
>where ((age(joining_date::date) like '5 years%') or
> (age(joining_date::date) like '10 years%') )
> 
> or
> 
>  create view vw_employee as
>select * from employees
>where
>   ((to_char(joining_date, '-MM') = to_char((now() - interval '5
> years'), '-MM') )
>or
>   (to_char(joining_date, '-MM') = to_char((now() - interval '10
> years'), '-MM')))
> 


Can this query be set up like :-

Consider the below scenarios :

Ram completed 5 years on 12/04/2014
Shyam completed 5 years on 21/04/2014
Ayan completed 10 years on 12/04/2014
and so on...

Now consider the current month is *march*. I have 12 employees. Out of which 
above only completed 5 and 10 years. Thus my output should come as

Name  milestoneswhen
Ram 512/04/2014
Shyam  5   21/04/2014
Ayan10  12/04/2014

-- 

Regards,
Arup Rakshit

Debugging is twice as hard as writing the code in the first place. Therefore, 
if you write the code as cleverly as possible, you are, by definition, not 
smart enough to debug it.

--Brian Kernighan


-- 
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 create a role with no privileges?

[Kynn Jones]

How does one define the most limited role/user possible in PostgreSQL?

Ideally, this role would not be able to do *anything* at all.  In
particular, this role would not be able to query meta-information about
existing tables, functions, etc. with backslash commands such as \dt, \df.

(Of course, in practice such a role would not correspond to any real role.
Its purpose, rather, is to serve as the starting point for defining more
realistic roles by selectively adding the fewest privileges possible).

TIA for any pointers!

kynn

Re: [GENERAL] how to create a role with no privileges?

[Jerry Sievers]

Kynn Jones  writes:

> How does one define the most limited role/user possible in PostgreSQL?
>
> Ideally, this role would not be able to do *anything* at all.  In 
> particular, this role would not be able to query meta-information about 
> existing tables, functions,
> etc. with backslash commands such as \dt, \df.

Some new role created and not granted anything has only public rights
which by default is the lowest level of privilege but as you probably
are aware does permit creating objects in public schema and viewing
certain system info.

But you can revoke usage on schemas; public, pg_catalog,
information_schema to create the illusion of even tighter than default
perms.

You would then need to grant usage on those schemas to some other role
and give this role to  real new roles/users who are permitted to do
those things.

>
> (Of course, in practice such a role would not correspond to any real role.  
> Its purpose, rather, is to serve as the starting point for defining more 
> realistic roles by
> selectively adding the fewest privileges possible).
>
> TIA for any pointers!
>
> kynn
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


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

[GENERAL] Cannot query views with WHERE clause on renamed columns

[Alexander Reichstadt]

Hi,

I try to do what seems to be totally simple, but it fails. If I query a view 
that contains renamed columns without any qualifier like

SELECT * FROM myview

it displays everything. If however I do 

SELECT reanmedviewcolumn FROM myview

It tells me column does not exist which in fact does. I can query any 
non-renamed columns. But the renamed ones I can neither call by their original 
name, not int he SELECT part nor in the WHERE clause.

I can neither leave the view with just the original names as it's a selfjoin.

What do I need to do?

Thanks
Alex


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

[GENERAL] lock contention, need profiling idea

[AI Rumman]

I see lots of similar log message at a certain time in a day on Postgresql
9,.1:

LOG:  process 18855 still waiting for ShareLock on transaction 2856146023
after 1001.209 ms
STATEMENT:  UPDATE table1 SET time = $1 WHERE id = $2

The table1 size is 17 G.

What could be the reason for this lock contention?
autovacuum?

Please give some idea.

Thanks.

Re: [GENERAL] Cannot query views with WHERE clause on renamed columns

[David G Johnston]

Alexander Reichstadt wrote
> Hi,
> 
> I try to do what seems to be totally simple, but it fails. If I query a
> view that contains renamed columns without any qualifier like
> 
> SELECT * FROM myview
> 
> it displays everything. If however I do 
> 
> SELECT reanmedviewcolumn FROM myview
> 
> It tells me column does not exist which in fact does. I can query any
> non-renamed columns. But the renamed ones I can neither call by their
> original name, not int he SELECT part nor in the WHERE clause.
> 
> I can neither leave the view with just the original names as it's a
> selfjoin.
> 
> What do I need to do?

Show us your actual code that is failing?

The general idea of what you are saying indeed works:

BEGIN;

CREATE VIEW myview AS SELECT 'one'::text AS one, 'two'::text AS two;

SELECT one FROM myview WHERE two = 'three';

ROLLBACK;

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cannot-query-views-with-WHERE-clause-on-renamed-columns-tp5809873p5809880.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] Cannot query views with WHERE clause on renamed columns

[Tom Lane]

Alexander Reichstadt  writes:
> I try to do what seems to be totally simple, but it fails. If I query a view 
> that contains renamed columns without any qualifier like

> SELECT * FROM myview

> it displays everything. If however I do 

> SELECT reanmedviewcolumn FROM myview

> It tells me column does not exist which in fact does.

What do you mean by a "renamed column"?  A concrete example would help
here.

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] how to create multiple databases running in different dirs

[frank ernest]

Hi, I'm new to postgresql and sql in general. I desired to write a program in C that used an sql data base for IPC and because multiple copies of my program might run on the same machine I wanted a way to ensure that only one copy of each multithreaded program got one database but I'm uncertain how to go about this. As the program might be run several different times and each time it should only get the data base from it's predisesor I can't use the pid as a unique data base name. I thought that I might start multiple pgsql instances but somehow that seems wrong. Any ideas?

 

Thanks, David

 

Re: [GENERAL] how to create multiple databases running in different dirs

[Rob Sargent]

On 06/30/2014 05:58 PM, frank ernest wrote:
Hi, I'm new to postgresql and sql in general. I desired to write a 
program in C that used an sql data base for IPC and because multiple 
copies of my program might run on the same machine I wanted a way to 
ensure that only one copy of each multithreaded program got one 
database but I'm uncertain how to go about this. As the program might 
be run several different times and each time it should only get the 
data base from it's predisesor I can't use the pid as a unique data 
base name. I thought that I might start multiple pgsql instances but 
somehow that seems wrong. Any ideas?

Thanks, David
Is each runtime instance of your application to have a new, empty 
database?  No data from any other run of the application?

Re: [GENERAL] lock contention, need profiling idea

[Michael Paquier]

On Tue, Jul 1, 2014 at 7:36 AM, AI Rumman  wrote:

> I see lots of similar log message at a certain time in a day on Postgresql
> 9,.1:
>
> LOG:  process 18855 still waiting for ShareLock on transaction 2856146023
> after 1001.209 ms
> STATEMENT:  UPDATE table1 SET time = $1 WHERE id = $2
>
> The table1 size is 17 G.
>
> What could be the reason for this lock contention?
> autovacuum?
>
This may be a CREATE INDEX query taking some time, perhaps combined with an
old prepared transaction still holding a lock? Perhaps a cron job running
behind that you are not aware of?
You should have a look at pg_stat_activity, pg_prepared_xacts and pg_locks
to get more information about the transactions running and the locks being
taken.
-- 
Michael

[GENERAL] pl/perl and recent perl versions - failing to load internal modules

[Toby Corkindale]

Hi,
I've been trying out PostgreSQL 9.3 with pl/perl built against Ubuntu 14.04 
LTS' Perl 5.18
(Sourced from apt.postgresql.org)

Maybe I'm doing something wrong, but it appears that plperl has become 
completely useless, as it can't load any new modules, but modern Perl versions 
have refactored the language into lots of small modules that are automatically 
loaded as required. Except they can't be loaded as required.

For instance, a fairly simple regex will try to load the modules: utf8, 
utf8_heavy & re
Since it is forbidden from loading anything, it will fail.

You can still use the unrestricted Perl module, plperlu, but this requires 
superuser permissions to use, and in the past its use has been discouraged.

I've experimented with using the plperl.on_init configuration option to try and 
include all required modules, but this requires trial-and-error and can easily 
cause new failures to appear if a stored procedure is changed a little.

Has anyone else encountered this? Am I doing something completely wrong?

Thanks,
Toby


-- 
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 create multiple databases running in different dirs

[John R Pierce]

On 6/30/2014 4:58 PM, frank ernest wrote:
Hi, I'm new to postgresql and sql in general. I desired to write a 
program in C that used an sql data base for IPC and because multiple 
copies of my program might run on the same machine I wanted a way to 
ensure that only one copy of each multithreaded program got one 
database but I'm uncertain how to go about this. As the program might 
be run several different times and each time it should only get the 
data base from it's predisesor I can't use the pid as a unique data 
base name. I thought that I might start multiple pgsql instances but 
somehow that seems wrong. Any ideas?


how would an instance of your program know what to connect to, or which 
previous instance its 'predecessor' was ?


normally, you have ONE database for a given set of applications, and all 
the applications share the same database tables and such.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

[GENERAL] debugging with child process

[Ravi Kiran]

hi,

can any one help me with the 4th and 5th steps of the following link which
is to debug with a child process in postgres.

*https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_with_child_processes
*


when I give the following fields in the debug configuration

*c/c++ Application : postgres*
*Project - psql*
*Build Configuration - Use Active*

I am getting the following error- * No source available for
"__kernel_vsyscall() at 0xb726a424" *


Is there any better tool other than eclipse to use for editing or knowing
about source code in postgres.

Thank you