Re: [GENERAL] Partitioning V schema

[Julian]

On 21/09/13 02:51, Gregory Haase wrote:


I would look towards how PostGis handles the Tiger census data for 
guidance. It's a similar, massive data set.


Greg Haase


I'm not sure why it wouldn't handle it fine?
The question is at what point would third party "imported" datasets, 
required for local lookup require their own dedicated solution (database 
and/or server)?
Especially when we are talking about a large amounts of data that come 
with their own unique global identifiers. Something like ISO 3166, even 
small, would do well with its own database - but not large enough to 
require a dedicated server.


This is the question I put to the OP, I would be interested to know.

Regards,
Julian


--
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 9.3 Installation Problems Windows 7 64 Bit Pro

[Jayadevan M]

It should be possible to install the SW without initializing the cluster.
That way you may be in a better position to troubleshoot the issue.
Complete the installation, then use initdb to initialize a cluster. Have a
look at this url too -
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Common_installation_errors


On Fri, Sep 20, 2013 at 5:55 PM, Tom Fraser  wrote:

> Hi,
>
> We are having a problem installing Postgres 9.3 on Windows 7 pro.
>
> The installation freezes and you can leave it running overnight without
> any error messages.
>
> The Bitrock log's last entries are:
>
> [14:45:43] Removing the existing ldconfig setting - set during the
> previous installation.
> [14:45:43] Running the post-installation/upgrade actions:
> [14:45:43] Write the base directory to the ini file...
> [14:45:43] Write the version number to the ini file...
>
> The program (User interface) says "Database cluster initalisation" or
> something like that
>
> We've tried uninstalling and reinstalling, as well as installing to a
> different drive. Checked permissions on the data directory..
>
> Does anyone have any suggestions? Thanks
>
>
>

[GENERAL] table inheritance and locks

[Manuel Kniep]

Hi,

If I aquire a lock on table B which is inherited from table A, Table A seems to 
be also locked (postgres 9.2).
Is there a way to get around it ?

I couldn't find any information about this behavior in the documentation.

Thanks 

Manuel Kniep



-- 
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 9.3 Installation Problems Windows 7 64 Bit Pro

[Tom Fraser]

Hi, 

 

We are having a problem installing Postgres 9.3 on Windows 7 pro.

 

The installation freezes and you can leave it running overnight without any 
error messages.

 

The Bitrock log's last entries are:

 

[14:45:43] Removing the existing ldconfig setting - set during the previous 
installation.
[14:45:43] Running the post-installation/upgrade actions:
[14:45:43] Write the base directory to the ini file...
[14:45:43] Write the version number to the ini file...

 

The program (User interface) says "Database cluster initalisation" or something 
like that

 

We've tried uninstalling and reinstalling, as well as installing to a different 
drive. Checked permissions on the data directory..

 

Does anyone have any suggestions? Thanks

 

 
  

[GENERAL] simple query with radically different plan after 9.0 -> 9.2 upgrade

[Kevin Goess]

Given this table

articles_1=> \d hits_user_daily_count;
  Table "public.hits_user_daily_count"
  Column   |  Type   | Modifiers
---+-+---
 userid| integer | not null
 date  | date| not null
 num_hits  | integer | default 0
 num_cover_page_hits   | integer | default 0
 num_additional_files_hits | integer | default 0
Indexes:
"hits_user_daily_count_pkey" PRIMARY KEY, btree (userid, date)
"hits_user_daily_count_date" btree (date)

whose data looks like this:

articles_1=> select * from hits_user_daily_count limit 5;
 userid  |date| num_hits | num_cover_page_hits |
num_additional_files_hits
-++--+-+---
 1590185 | 2013-07-10 |3 |   4 |
  0
  391907 | 2013-07-10 |   16 |  12 |
  0
 1739541 | 2013-08-03 |1 |   0 |
  0
 1798435 | 2013-07-10 |0 |   1 |
  0
 1521468 | 2013-07-10 |2 |   0 |
  0

We noticed a big change after upgrading from 9.0 to 9.2. For
*yesterday‘s*date, the query plan is fine, like you’d expect

articles_1=> explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-07-30';

QUERY PLAN
--
 Index Only Scan using hits_user_daily_count_pkey on
hits_user_daily_count  (cost=0.00..8.02 rows=1 width=0) (actual
time=0.02
   Index Cond: ((userid = 1590185) AND (date = '2013-07-30'::date))
   Heap Fetches: 1
   Buffers: shared hit=5
 Total runtime: 0.044 ms

but for *today‘s* date it looks like it’s reading all the rows for that
date, 15,277 buffers:

articles_1=> explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-08-01';

QUERY PLAN
--
 Index Scan using hits_user_daily_count_date on hits_user_daily_count
(cost=0.00..7.92 rows=1 width=0) (actual time=11.957..1
   Index Cond: (date = '2013-08-01'::date)
   Filter: (userid = 1590185)
   Rows Removed by Filter: 20149
   Buffers: shared hit=15277
 Total runtime: 17.924 ms

(The dates in the queries are old because I've had this email in draft for
a while, but the behavior is still identical).

We‘ve addressed the problem by running ’analyze' on the table every day ate
about 1:30am. Buffer hits on that table go from about 1,000/sec to
70,000/sec between midnight and that analyze job, and then go back down to
1,000/sec and stay flat until midnight rolls around again.

Before the 9.0 -> 9.2 upgrade, the behavior would be flat all day.

Any ideas what would be causing that problem?

Re: [GENERAL] Partitioning V schema

[Gregory Haase]

I would look towards how PostGis handles the Tiger census data for
guidance. It's a similar, massive data set.

Greg Haase
On Sep 20, 2013 9:47 AM, "Jeff Janes"  wrote:

> On Thu, Sep 19, 2013 at 12:02 AM, Dave Potts wrote:
>
>> Hi List
>>
>> I am looking for some general advice about the best was of splitting  a
>> large data table,I have  2 different choices, partitioning or different
>> schemas.
>>
>
>
> I don't think there is much of a choice there.  If you put them in
> different schemas, then you are inherently partitioning the data.  It just
> a question of how you name your partitions, which is more of a naming issue
> than a performance issue.
>
>
>>
>> The data table refers to the number of houses that can be include in a
>> city, as such there are large number of records.
>>
>>
>> I am wondering if decided to partition the table if the update
>> speed/access might be faster that just declaring a different schema per
>> city.
>>
>
> If you partition based on city, then there should be no meaningful
> difference.  If you partition based on something else, you would have to
> describe what it is partitioned on, and what your access patterns are like.
>
> Cheers,
>
> Jeff
>

Re: [GENERAL] PostgreSQL SQL Tricks: faster urldecode

[Marc Mamin]

> Von: Merlin Moncure [mmonc...@gmail.com]
> Gesendet: Freitag, 20. September 2013 17:43
> 
> >  On Fri, Sep 20, 2013 at 10:26 AM, Marc Mamin  wrote:
> > Hi,
> > here is a function which is about 8 x faster than the one described in the 
> > PostgreSQL SQL Tricks
> > ( 
> > http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code
> >  )
> >
> > The idea is to handle each encoded/not_encoded parts in bulk rather than 
> > spliting on each character.
> >
> > urldecode_arr:
> > Seq Scan on lt_referrer  (actual time=1.966..17623.979 rows=65717 loops=1)
> >
> > urldecode:
> > Seq Scan on lt_referrer  (actual time=4.846..15.292 rows=65717 loops=1)
> 
> very nice.  Basically it comes down to this: all non-trivial regex
> replacements require decomposition of the string into an array because
> regexp_replace() is unable to do any kind of transformation on the
> string.  This is a crippling limitation relative to first-class regex
> languages like perl; postgres string translation functions are
> invisible to the regex engine.  I have no idea if this is fixable (I
> dimly recall Tom explaining why it might not be).
> 
> merlin

yes, a possible(?) assistance for such problems would be a new variant of 
regexp_split_to_table
that would return two columns: 
- the splitted parts (as currently)
- the separator matches (new)

Marc




-- 
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] Partitioning V schema

[Jeff Janes]

On Thu, Sep 19, 2013 at 12:02 AM, Dave Potts  wrote:

> Hi List
>
> I am looking for some general advice about the best was of splitting  a
> large data table,I have  2 different choices, partitioning or different
> schemas.
>


I don't think there is much of a choice there.  If you put them in
different schemas, then you are inherently partitioning the data.  It just
a question of how you name your partitions, which is more of a naming issue
than a performance issue.


>
> The data table refers to the number of houses that can be include in a
> city, as such there are large number of records.
>
>
> I am wondering if decided to partition the table if the update
> speed/access might be faster that just declaring a different schema per
> city.
>

If you partition based on city, then there should be no meaningful
difference.  If you partition based on something else, you would have to
describe what it is partitioned on, and what your access patterns are like.

Cheers,

Jeff

Re: [GENERAL] PostgreSQL SQL Tricks: faster urldecode

[Merlin Moncure]

On Fri, Sep 20, 2013 at 10:26 AM, Marc Mamin  wrote:
> Hi,
> here is a function which is about 8 x faster than the one described in the 
> PostgreSQL SQL Tricks
> ( 
> http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code
>  )
>
> The idea is to handle each encoded/not_encoded parts in bulk rather than 
> spliting on each character.
>
> urldecode_arr:
> Seq Scan on lt_referrer  (actual time=1.966..17623.979 rows=65717 loops=1)
>
> urldecode:
> Seq Scan on lt_referrer  (actual time=4.846..15.292 rows=65717 loops=1)

very nice.  Basically it comes down to this: all non-trivial regex
replacements require decomposition of the string into an array because
regexp_replace() is unable to do any kind of transformation on the
string.  This is a crippling limitation relative to first-class regex
languages like perl; postgres string translation functions are
invisible to the regex engine.  I have no idea if this is fixable (I
dimly recall Tom explaining why it might not be).

merlin


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

[GENERAL] PostgreSQL SQL Tricks: faster urldecode

[Marc Mamin]

Hi,
here is a function which is about 8 x faster than the one described in the 
PostgreSQL SQL Tricks
( 
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code 
)

The idea is to handle each encoded/not_encoded parts in bulk rather than 
spliting on each character.

urldecode_arr:
Seq Scan on lt_referrer  (actual time=1.966..17623.979 rows=65717 loops=1)

urldecode:
Seq Scan on lt_referrer  (actual time=4.846..15.292 rows=65717 loops=1)

regards,

Marc Mamin



CREATE OR REPLACE FUNCTION urldecode_arr(url text)
  RETURNS text AS
$BODY$
DECLARE ret text;

BEGIN
 BEGIN

WITH STR AS (
  SELECT
  
  -- array with all non encoded parts, prepend with '' when the string 
start is encoded
  case when $1 ~ '^%[0-9a-fA-F][0-9a-fA-F]' 
   then array[''] 
   end 
  || regexp_split_to_array ($1,'(%[0-9a-fA-F][0-9a-fA-F])+', 'i') plain,
  
  -- array with all encoded parts
  array(select (regexp_matches ($1,'((?:%[0-9a-fA-F][0-9a-fA-F])+)', 
'gi'))[1]) encoded
)
SELECT  string_agg(plain[i] || coalesce( 
convert_from(decode(replace(encoded[i], '%',''), 'hex'), 'utf8'),''),'')
FROM STR, 
  (SELECT  generate_series(1, array_upper(encoded,1)+2) i FROM STR)blah

INTO ret;

  EXCEPTION WHEN OTHERS THEN  
raise notice 'failed: %',url;
return $1;
  END;   

  RETURN coalesce(ret,$1); -- when the string has no encoding;

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT


-- 
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] recover deleted data

[Kevin Grittner]

AI Rumman  wrote:

> Is there any way to recover deleted record in Postgresql 9.2 if
> we don't have any backup.

I would recommend following the advice here *immediately*:

http://wiki.postgresql.org/wiki/Corruption

Any recoverable data may be disappearing through normal maintenance
operations as long as the server is running.

Then follow the advice in the "What next?" section of that page.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] recover deleted data

[AI Rumman]

Is there any way to recover deleted record in Postgresql 9.2 if we don't
have any backup.

Thanks.

Re: [GENERAL] Tree structure

[hari . fuchs]

Kaare Rasmussen  writes:

> Hi
>
> I'm trying to determine the best way to represent a simple tree
> structure (like a file/dir tree or a uri path). I guess that's done a
> zillion times before; I just don't seem to be able to find the right
> solution. I have one special request, that I'd like to find all
> shorter' paths, i.e. given 'a/b/c/d' it'll find
>
> a
> a/b
> a/b/c
> - but not
> b
> a/c
> b/a

If I understand you correctly, you want a prefix match, and sure there's
a PostgreSQL extension for that:

CREATE EXTENSION prefix;

CREATE TABLE t1 (
  id serial NOT NULL,
  p prefix_range NOT NULL,
  PRIMARY KEY (id)
);

CREATE INDEX pp ON t1 USING gist(p);

INSERT INTO t1 (p) VALUES
('a'),
('b'),
('a/c'),
('a/b'),
('b/a'),
('a/b/c');

EXPLAIN ANALYZE
SELECT id, p
FROM t1
WHERE p @> 'a/b/c/d'
;



-- 
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] reading cvs logs with pgadmin queries

[Adrian Klaver]

On 09/19/2013 06:04 PM, Dave Cramer wrote:

Adrian,




2) The exact error message is  ERROR:  extra data after last expected column


considering ~39000 lines go in before this line I am fairly certain it is the 
line.


New day, new start. I am not sure now that the line you showed is the 
problem. I isolated that line in a file and did a COPY into postgres 
log. Note, I changed the time zone because my installation did not 
recognize WST:


test=# COPY postgres_log FROM '/home/postgres/pg_csv_test.csv' WITH csv;
COPY 1
test=# \x
Expanded display is on.
test=# SELECT * from postgres_log ;
-[ RECORD 1 
]--+

log_time   | 2013-09-12 10:33:19.145-07
user_name  | user
database_name  | dbname
process_id | 14581
connection_from| 192.168.1.22:58840
session_id | 523126d3.38f5
session_line_num   | 1
command_tag| SELECT
session_start_time | 2013-09-12 10:28:35-07
virtual_transaction_id | 6/503023
transaction_id | 0
error_severity | ERROR
sql_state_code | 42P01
message| relation "dds_stores" does not exist
detail |
hint   |
internal_query |
internal_query_pos |
context|
query  | WITH RECURSIVE recursive_stores(id, name) AS (
   | SELECT id, name FROM customer_store 
WHERE id IN (1, 280, 864, 2376)

   | UNION ALL
   | SELECT ss.id, ss.name FROM 
recursive_stores sss, customer_store

   | ss WHERE sss.id = ss.parent_store_id
   | ) SELECT
   | to_char(i.timestamp, 'Mon-YY') AS 
"Month Name",

   | s.name AS "Target",
   | COUNT(DISTINCT i.id) / (SELECT 
COUNT(dds_stores.id) FROM

   | dds_stores) AS "Ticket Count"
   | FROM
   | customer_store s
   | INNER JOIN printdata_workstation w ON 
s.id = w.store_id AND s.id
   | IN (SELECT recursive_stores.id FROM 
recursive_stores)
   | INNER JOIN printdata_report r ON w.id 
= r.workstation_id AND

   | r.package_id IS NOT NULL
   | INNER JOIN printdata_page p ON r.id = 
p.report_id
   | INNER JOIN printdata_item i ON p.id = 
i.page_id

   | WHERE
   | r.timestamp >= '2012-09-01' AND 
r.timestamp <= '2013-08-31'

   | GROUP BY
   | "Month Name",
   | "Target"
query_pos  | 367
location   |
application_name   | pgAdmin III - Query Tool






Dave




--
Adrian Klaver
adrian.kla...@gmail.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] Tree structure

[Alban Hertroys]

> 1. As strings
>   There's no dedicated function (@>)
>   WHERE clause should read something like 'a/b/c/d' LIKE column || '%',
> which is both ugly and (I guess) non indexable
>   Perhaps regex indexes would work, but not efficient and not optimal
>
> 2. As array of strings
>   My favorite, would be elegant. A GIN index on the whole array would make
> for fast performance
>   Alas @> treats the arrays as a set, not an array
>   WHERE col @> 'a/b/c/d' would find all of the above rows, including a, a/c,
> b/a, etc.
>
> 3. ltree contrib
>   The only option that actually works and uses index
>   @> works as I want it to.
>   But the single segments can only be alphanumeric and underscore
>   ltree only supports GIST
>   there's a length limit.

4. Using a recursive common table expression (CTE).
  http://www.postgresql.org/docs/9.2/static/queries-with.html

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
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] Using oracle stored procedures for postgresql database

[Pavel Stehule]

Hello


2013/9/20 BOUVARD Aurélien 

> Hi ,
>
> Maybe it will help you : http://orafce.projects.pgfoundry.org/
>

orafce was moved to github https://github.com/orafce/orafce

Regards

Pavel


>
> Regards,
>
>
> Aurélien
>
>
>  Afin de contribuer au respect de l'environnement, merci de n'imprimer ce
> mail qu'en cas de nécessité.
>
>
> -Message d'origine-
> De : pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] De la part de nikhilruikar
> Envoyé : vendredi 20 septembre 2013 11:50
> À : pgsql-general@postgresql.org
> Objet : [GENERAL] Using oracle stored procedures for postgresql database
>
> Hi
>   I am planning to migrate oracle database to postgres. I need to know
> if there are any plugins or tool with which I can use stored procedures
> written in oracle schema for postgres with out changing them.
>
> Thanks in advance
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Using-oracle-stored-procedures-for-postgresql-database-tp5771714.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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Tree structure

[Kaare Rasmussen]

Hi

I'm trying to determine the best way to represent a simple tree 
structure (like a file/dir tree or a uri path). I guess that's done a 
zillion times before; I just don't seem to be able to find the right 
solution. I have one special request, that I'd like to find all 
'shorter' paths, i.e. given 'a/b/c/d' it'll find


a
a/b
a/b/c
- but not
b
a/c
b/a

There are a number of options to test.

1. As strings
  There's no dedicated function (@>)
  WHERE clause should read something like 'a/b/c/d' LIKE column || '%', 
which is both ugly and (I guess) non indexable

  Perhaps regex indexes would work, but not efficient and not optimal

2. As array of strings
  My favorite, would be elegant. A GIN index on the whole array would 
make for fast performance

  Alas @> treats the arrays as a set, not an array
  WHERE col @> 'a/b/c/d' would find all of the above rows, including a, 
a/c, b/a, etc.


3. ltree contrib
  The only option that actually works and uses index
  @> works as I want it to.
  But the single segments can only be alphanumeric and underscore
  ltree only supports GIST
  there's a length limit.

The last option is the one I'm using right now, but I hope that somebody 
can point out where I'm wrong with regards to the other options, or tell 
me that there is a better solution somewhere I didn't look.



--
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] Using oracle stored procedures for postgresql database

[Jayadevan M]

Hi,

Have a look at orafce for the plugins. Try ora2pg for initial migration.
There are differences in how you execute procedures (syntax), packages are
missing in PostgreSQL and so on. Please have a look at
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle

Regards,
Jayadevan


On Fri, Sep 20, 2013 at 3:19 PM, nikhilruikar wrote:

> Hi
>   I am planning to migrate oracle database to postgres. I need to know
> if there are any plugins or tool with which I can use stored procedures
> written in oracle schema for postgres with out changing them.
>
> Thanks in advance
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Using-oracle-stored-procedures-for-postgresql-database-tp5771714.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] Using oracle stored procedures for postgresql database

[BOUVARD Aurélien]

Hi ,

Maybe it will help you : http://orafce.projects.pgfoundry.org/ 

Regards,


Aurélien 


 Afin de contribuer au respect de l'environnement, merci de n'imprimer ce mail 
qu'en cas de nécessité.


-Message d'origine-
De : pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] De la part de nikhilruikar
Envoyé : vendredi 20 septembre 2013 11:50
À : pgsql-general@postgresql.org
Objet : [GENERAL] Using oracle stored procedures for postgresql database

Hi 
  I am planning to migrate oracle database to postgres. I need to know if 
there are any plugins or tool with which I can use stored procedures written in 
oracle schema for postgres with out changing them.

Thanks in advance



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Using-oracle-stored-procedures-for-postgresql-database-tp5771714.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


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

[GENERAL] Using oracle stored procedures for postgresql database

[nikhilruikar]

Hi 
  I am planning to migrate oracle database to postgres. I need to know
if there are any plugins or tool with which I can use stored procedures
written in oracle schema for postgres with out changing them.

Thanks in advance



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Using-oracle-stored-procedures-for-postgresql-database-tp5771714.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] How to failover from Primary to Standby and Set the old Primary as a new Standby

[ascot.m...@gmail.com]

Hi,

How about the following two areas?

step 3: wait until the failover is completed, the server is brought up after 
applying all WAL files available in the archive. 
Question 1: How to check if the failover is completed and the new Primary is 
ready?

step 4: if the failover is done
Question 2: Do I need to edit the new primary's postgresql.conf and restart 
postgresql? e.g. comment out the hot_standy = on 

thanks

On 20 Sep 2013, at 4:39 AM, Michael Nolan wrote:

> On 9/19/13, John R Pierce  wrote:
>> On 9/19/2013 1:29 PM, Vick Khera wrote:
>>> 
>>> On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com
>>>  >> > wrote:
>>> 
>>>I use PG 9.2.4 with streaming replication.  What will be the
>>>manual procedure to failover from Primary to Standby and Set the
>>>old Primary as a new standby?
>>> 
>>> 
>>> From what I understand, you start over by setting up the old primary
>>> as a new standby from scratch.
>> 
>> if you use rsync for the base backup of new master to old, it should go
>> fairly quickly as relatively few files should have changed assuming not
>> much time has elapsed.
> 
> Of course, before you do anything, you should spend some time figuring
> out WHY the old master failed.  There could be issues that need to be
> resolved before putting it back online, and fixing them could affect
> how much work you have to do to get the physical files back in sync.
> --
> Mike Nolan
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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