Hello,
I was trying to figure out how does one recover server statistics to the
same snapshot to which a database is restored after PITR.
The steps i had in mind were
1.Set up WAL archiving
2.On server shutdown one would need to backup pg_stat_tmp along with file
system level back of database
3.
Hey All,
This is a message to confirm my thoughts / validate a possible approach.
In a situation where PGDATA and {XLOG, ARCHIVELOG} are on different SAN/NAS
volumes and a backup is to be initiated do pg_start_backup and
pg_stop_backup need to be used?
I am using snapshots of each volume for
On Thu, Jun 20, 2013 at 3:17 PM, Sameer Thakur samthaku...@gmail.com wrote:
Hello,
I was trying to figure out how does one recover server statistics to the
same snapshot to which a database is restored after PITR.
The steps i had in mind were
1.Set up WAL archiving
2.On server shutdown one
Hello
I wrote lot of C VARIADIC functions - some examples are in core -
format function
Schema | Name| Result data type | Argument data types | Type
+---+--+--+
pg_catalog | concat| text | VARIADIC any
Hello:
I have question about PG's create index concurrently. I think it is a
bug perhaps.
I make two tables tab01 and tab02, they have no relationships.
I think create index concurrently on tab02 will not be influenced by
transaction on tab01.
But the result differs:
My first
On Thu, Jun 20, 2013 at 7:24 AM, James Sewell james.sew...@lisasoft.comwrote:
Hello All,
I have the following config:
hostsamerole+myrole samenetldap
ldapserver=ldap1,ldap2,ldap3 ldapbinddn=mybinddn
ldapbindpasswd=mypass ldapbasedn=mybase
On Thu, Jun 20, 2013 at 8:45 AM, James Sewell james.sew...@lisasoft.comwrote:
Hey All,
This is a message to confirm my thoughts / validate a possible approach.
In a situation where PGDATA and {XLOG, ARCHIVELOG} are on different
SAN/NAS volumes and a backup is to be initiated do
Documentation mentions following:
Thanks, but how does this relate to statistics recovery wrt PITR?
regards
Sameer
On 18/06/13 18:31, bhanu udaya wrote:
Hello,
Greetings.
My PostgresSQL (9.2) is crashing after certain load tests. Currently,
postgressql is crashing when simulatenously 800 to 1000 threads are run
on a 10 million records schema. Not sure, if we have to tweak some more
parameters of postgres.
On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur samthaku...@gmail.com wrote:
Documentation mentions following:
Thanks, but how does this relate to statistics recovery wrt PITR?
Upon clean server shutdown, you have the statistics files stored in
the pg_stat (previously global/) directory, which
On Thu, Jun 20, 2013 at 5:17 AM, Richard Huxton d...@archonet.com wrote:
On 18/06/13 18:31, bhanu udaya wrote:
Hello,
Greetings.
My PostgresSQL (9.2) is crashing after certain load tests. Currently,
postgressql is crashing when simulatenously 800 to 1000 threads are run
on a 10 million
guilherme wrote
I need to get some information in database and export it.
Is there a way to make PostgreSQL to export one data line to a new file?
Like this:
1 FIRST LINE -- line1.txt;
2 SECOND LINE -- line2.txt;
3 THIRD LINE -- line3.txt
...
and so...
I know that I can import all
Hi all,
I do not understand why postgreSQL estimates an index scan only half as fast as
a seq scan:
system=# explain select * from queuelog;QUERY PLAN
---
Seq Scan on queuelog (cost=0.00..20530.29
On 06/20/2013 06:06 AM, David Johnston wrote:
guilherme wrote
I need to get some information in database and export it.
Is there a way to make PostgreSQL to export one data line to a new file?
Like this:
1 FIRST LINE -- line1.txt;
2 SECOND LINE -- line2.txt;
3 THIRD LINE -- line3.txt
...
and
Hi all,
I seem to be having problems constructing a query that combines the use of
distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate C;
ERROR: collations are not supported by type integer
LINE 1: ... distinct(value) from
On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote:
OP needs to explore use of connection pooler, in particular pgbouncer.
Anyways none of this explains why the server is actually crashing.
It might be hitting file descriptor limits. I didn't dig into the earlier part
of this thread
On 06/20/2013 07:05 AM, Tim Kane wrote:
Hi all,
I seem to be having problems constructing a query that combines the use
of distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate C;
ERROR: collations are not supported by type
Nice one. Yep, that works. Cheers..
I'll submit a bug report for this, though I'm on the fence as to wether
this is actually a bug per se.. I would have reasonably expected my
original syntax to have worked (as it does without any ORDER BY)..
On Thu, Jun 20, 2013 at 3:44 PM, Adrian Klaver
Hello,
I have a database server which do a complex views calculation, the result of
those views are shipped to another database servers via a simple replication
tool which have a high client loads.
The tool is creating a table, and indexes based on predefined conf., then
drop the
Hi All,
I am using coalesce(firstname,lastname), to get the result if first name is
'NULL' it will give me lastname or either way. I am having data like
instead of NULL, blank null ( i mean something like '' ) for which
coalesce is not working, is there any workaround or other function
available
Torsdag 20. juni 2013 21.45.02 skrev itishree sukla:
Hi All,
I am using coalesce(firstname,lastname), to get the result if first name is
'NULL' it will give me lastname or either way. I am having data like
instead of NULL, blank null ( i mean something like '' ) for which
coalesce is not
Hi,
http://www.postgresql.org/docs/9.1/static/functions-conditional.html describes
NULLIF, when combined with COALESCE it should answer your request.
HTH
Kind regards/met vriendelijke groet,
Serge Fonville
http://www.sergefonville.nl
Convince Microsoft!
They need to add TRUNCATE PARTITION in
I need to get some information in database and export it.
Is there a way to make PostgreSQL to export one data line to a new file?
Like this:
1 FIRST LINE -- line1.txt;
2 SECOND LINE -- line2.txt;
3 THIRD LINE -- line3.txt
...
and so...
I know that I can import all information into a unique
Hello,
I have table with 37 million entries the whole table has a size of 2.3 GB
Although I have set the work_mem to 10 GB
I see the an unexpected external sort Disk in Explain Analyze for around 650MB
of data
EXPLAIN ANALYZE SELECT application_id, price_tier FROM application_prices
On Thu, Jun 20, 2013 at 1:27 AM, 高健 luckyjack...@gmail.com wrote:
Hello:
I have question about PG's create index concurrently. I think it is a
bug perhaps.
I make two tables tab01 and tab02, they have no relationships.
I think create index concurrently on tab02 will not be
On Thu, Jun 20, 2013 at 6:47 AM, Antonio Goméz Soto
antonio.gomez.s...@gmail.com wrote:
Hi all,
I do not understand why postgreSQL estimates an index scan only half as
fast as a seq scan:
system=# explain select * from queuelog;QUERY
PLAN
On Thu, Jun 20, 2013 at 6:12 AM, Manuel Kniep rap...@adeven.com wrote:
Hello,
I have table with 37 million entries the whole table has a size of 2.3 GB
Although I have set the work_mem to 10 GB
There is one piece of memory used in in-memory sorting that (currently) has
to be a single
On 06/17/2013 04:00 PM, Joshua D. Drake wrote:
http://postgresql.1045698.n5.nabble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-PostgreSQL-Performance-td5735284.html
tl;dr for that thread seems to be a driver problem (fusionIO?), I'm
unsure if Ubuntu specific or in the upstream kernel.
That instance
On Jun 20, 2013, at 14:33, guilherme guilhe...@quirius.com.br wrote:
I need to get some information in database and export it.
Is there a way to make PostgreSQL to export one data line to a new file?
Like this:
1 FIRST LINE -- line1.txt;
2 SECOND LINE -- line2.txt;
3 THIRD LINE --
I am having some problems moving a column to another table and fixing
some views that rely on it. I want to move the area_id column from
t_offerprice_pipe to t_offerprice and then left join the results.
When I have only one table I get the correct results. area_id is
currently in the
Good to know. I've got a few spare machines I might be able to test
3.2 kernels on in the next few months
On Thu, Jun 20, 2013 at 12:54 PM, Shaun Thomas stho...@optionshouse.com wrote:
On 06/17/2013 04:00 PM, Joshua D. Drake wrote:
Jason Long-2 wrote
I am having some problems moving a column to another table and fixing
some views that rely on it. I want to move the area_id column from
t_offerprice_pipe to t_offerprice and then left join the results.
When I have only one table I get the correct results. area_id is
On Thu, Jun 20, 2013 at 12:22 AM, Andrew Bartley ambart...@gmail.com wrote:
Sorry that should be aggregate int_array_aggregate not function
On 20 June 2013 08:16, Andrew Bartley ambart...@gmail.com wrote:
Hi All,
I am trying to use the intagg extension. in 9.1.9
I have created the
Andrew Bartley ambart...@gmail.com writes:
function int_array_aggregate(integer[]) does not exist
int_array_aggregate() takes integers, not arrays of integers.
Depending on exactly what semantics you'd like to have, you could
probably build a custom aggregate to do this without any new C code
itishree sukla wrote
Hi All,
I am using coalesce(firstname,lastname), to get the result if first name
is
'NULL' it will give me lastname or either way. I am having data like
instead of NULL, blank null ( i mean something like '' ) for which
coalesce is not working, is there any workaround
David,
Thank you very much for your response.
Below is a script that will reproduce the problem with comments
included.
/***/
--drop table t_item;
--drop table t_price_base_table;
--drop table t_price_original_with_area_id;
--this table
On Fri, Jun 21, 2013 at 7:36 AM, David Johnston pol...@yahoo.com wrote:
SELECT input
FROM ( SELECT unnest($1) AS input ) src
WHERE input IS NOT NULL AND input ''
LIMIT 1;
Does this guarantee the order of the results returned? Using LIMIT
without ORDER BY is
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
I am rewriting a large number of views and I want to make sure that
nothing is changes in the results.
Something like
select compare_results('select * from v_old', 'select * from
Jason Long-2 wrote
David,
Thank you very much for your response.
Below is a script that will reproduce the problem with comments
included.
/*
This is the new query that is not working correctly.
I am trying to left join the base table by its id and area_id.
I need a left
On Thu, 2013-06-20 at 15:37 -0700, David Johnston wrote:
Jason Long-2 wrote
David,
Thank you very much for your response.
Below is a script that will reproduce the problem with comments
included.
/*
This is the new query that is not working correctly.
I am trying
Jason Long-2 wrote
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
First thing that comes to mind:
WITH
before_qry (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) )
, after_qry (col1, col2, col3) AS ( VALUES
On Thu, Jun 20, 2013 at 3:18 PM, Jason Long
mailing.li...@octgsoftware.comwrote:
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
I am rewriting a large number of views and I want to make sure that
nothing is changes in the results.
Thank you. I will give it a try. I have never used WITH before.
Thank you for the tips.
On Thu, 2013-06-20 at 16:05 -0700, David Johnston wrote:
Jason Long-2 wrote
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
First thing
Jason Long-2 wrote
Jason Long-2 wrote
There is a unique constraint on the real price table. I hadn't thought
of how I will enforce the constraint across two tables.
size_id and area_id will have to be unique across both
t_price_base_table and t_price_original_with_area_id. I will want
Hey,
Thanks for the reply Magnus.
I'm getting some packet captures now - I just thought I'd throw this out
there in case anyone else had faced similar problems.
This is EDB PPAS, I'm following up with them in parallel.
Cheers,
James Sewell
James Sewell
Solutions Architect
Thanks Magnus,
Could you elaborate a bit more on this?
I've been having a look at do_pg_start_backup() and I can't really see
anything apart from enabling full page writes and running a checkpoint to
avoid getting a torn page. I could be missing something easily though, as
I'm not familiar with
Thanks Jeff
But What I can't understand is:
In My first test, the create index concurrently works well.
In My second test, the create index concurrently can not work.
The difference is only on ecpg's select statement :
One use host variable of char (its value is of integer 14) in select
I was given a dump of an existing remote schema and database, and the
restore on my local system failed. Looking into it, I found a circular
parent-child/child-parent relationship, and I don't believe this existing
structure is viable. To summarize, the organization entity has an attribute
of
On Thu, Jun 20, 2013 at 8:32 PM, Amit Langote amitlangot...@gmail.com wrote:
On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur samthaku...@gmail.com wrote:
Documentation mentions following:
Thanks, but how does this relate to statistics recovery wrt PITR?
Upon clean server shutdown, you have the
Melvin Call melvincall...@gmail.com writes:
I was given a dump of an existing remote schema and database, and the
restore on my local system failed. Looking into it, I found a circular
parent-child/child-parent relationship, and I don't believe this existing
structure is viable. To summarize,
On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote:
Jason Long-2 wrote
Jason Long-2 wrote
There is a unique constraint on the real price table. I hadn't thought
of how I will enforce the constraint across two tables.
size_id and area_id will have to be unique across both
Jason Long-2 wrote
Does the syntax you showed me have performance benefits vs joining a
bunch of views together?
As a general rule CTE/WITH is going to be worse performing than the
equivalent view definition - depending on the view is actually used in the
query of course. They both have their
You need to have statistics recovered to the same state as they were
when you took the FS level backup of your database after shutting down
the server.
Correct
Shutting down is important since that is when you would have
statistics files ($PGDATA/pg_stat/*.stat) available to backup.
PostgreSQL has no such capability. Unless you need that and
want to code it yourself, the best solution would be to
write a function that just ignores the third argument.
For time being I will write a function that just ignores the third argument.
but if we really want to create such
54 matches
Mail list logo