Re: [GENERAL] Streaming replication with sync slave, but disconnects due to missing WAL segments

HiThanks for your reply. Do you know of any options that I could give pg_dump/psql to avoid creating one big transaction? I'm using the plain text format for pg_dump.Best regards,Mads-pgsql-general-ow...@postgresql.org skrev: -Til: Albe Laurenz Fra: Jeff Janes Sendt af: pgsql-general-ow...@

Re: [GENERAL] How to modify dump files created by pg_dump

Hmm...It seem like a wonderful solution. But, i can't change the way to restore. For some reason, the way to restore as follows. pg_restore -C -h xxx -p xxx -U xxx -d template1 -e -v xxx.pgdump Does any one can give a clarification about whether the dump file support modify? By the way, dump wi

Re: [GENERAL] How to modify dump files created by pg_dump

On 06/05/2013 06:17 PM, 任洪彩 wrote: Hmm...It seem like a wonderful solution. But, i can't change the way to restore. Then you will not be able to do what you want. For some reason, the way to restore as follows. pg_restore -C -h xxx -p xxx -U xxx -d template1 -e -v xxx.pgdump Does any one can

Re: [GENERAL] Trouble with replication

On Thu, Jun 6, 2013 at 7:23 AM, David Greco wrote: > On the master or on the slave, or on both? I thought shipping the archived > WAL files from the master to the slave did this already? > > In your case you need to transfer the WAL files using streaming replication, so you need to set wal_keep_

Re: [GENERAL] How to modify dump files created by pg_dump

On 06/05/2013 05:20 AM, 私人邮箱 wrote: > no，I can't... > > Actually the dump file comes from another site... So use the -f option to pg_restore to save the custom format file to a text file. Edit the text file and then run using psql. Ex: pg_restore -C -f whatever_name.sql /root/horen/rnwdump/

Re: [GENERAL] [postgis-users] point_ops with GiST PostGIS Spatial Index

Pardon my lack of specificity. I'm familiar with spatial indexes and at least somewhat familiar with ST_Contains and its internal bounding box check. Maybe it would help to clarify why I found this point_ops operator class so interesting. Currently, my queries are often getting bad estimates for th

Re: [GENERAL] Trouble with replication

From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of John R Pierce [pie...@hogranch.com] Sent: Wednesday, June 05, 2013 5:00 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with replication On 6/5/2013 1:39 PM, David Greco wrote: I’ve setup

Re: [GENERAL] Function use in query

Ioana Danes wrote > Hi All, > Is there any similar syntax that only invokes the procedure once and > returns all the columns? Generic, adapt to fit your needs. WITH func_call AS ( SELECT function_call(...) AS func_out_col ) SELECT (func_out_col).* FROM func_call; Basically you have to execute t

Re: [GENERAL] Trouble with replication

On 6/5/2013 1:39 PM, David Greco wrote: I've setup two 9.2.4 servers to serve as master-slave in a streaming replication scenario. I started with a fresh database on the master, setup the replication, then imported using pg_restore about 30GB of data. The master and slave are geographically

[GENERAL] Trouble with replication

I've setup two 9.2.4 servers to serve as master-slave in a streaming replication scenario. I started with a fresh database on the master, setup the replication, then imported using pg_restore about 30GB of data. The master and slave are geographically separated, so replication of this amount of

Re: [GENERAL] [postgis-users] point_ops with GiST PostGIS Spatial Index

Create the gist index on table containing points using the following syntax: CREATE INDEX ON USING GIST (); After the index is created use the criteria st_contains(polygon,point) in the where clause of select statement: Select * from where st_contains(polygon, point) ; Regards, Kedar Tamb

[GENERAL] Function use in query

Hi All, I would like to ask for some suggestions regarding the following scenario. I have a cash drawer table and for each cash drawer I have a function that collects and transforms data from different tables (and a web service using www_fdw). In normal scenarios I would have a function to r

Re: [GENERAL] What's a good way to improve this query?

Well, your objects are larger than the page size, so you're getting them out of the toast tables, not directly out of main storage. You may also have your type declared as 'main' storage, which means it's zipped up, so it's being unzipped before you can access it, that's also an overhead. For

[GENERAL] point_ops with GiST PostGIS Spatial Index

I posted this question on StackOverflow, and the only person to answer recommended I ask these lists for more details and link to the question: http://stackoverflow.com/questions/16927331/postgresql-point-ops-with-gist-postgis-spatial-index My question is: The 9.0 release notes

[GENERAL] What's a good way to improve this query?

Hello, I'm running this PostGIS Raster query select st_scalex(rast), st_scaley(rast), st_skewx(rast), st_skewy(rast), st_width(rast), st_height(rast), rid, st_upperleftx(rast), st_upperlefty(rast), st_numbands(rast) from my_postgis_raster_table I want to remark that, even when 'ra

[GENERAL] Synonyms in PostgreSQL 9.2.4

Hi, I am currently working on a conversion project. We plan to make our application PostgreSQL compliant. And for this we have chosen PosgreSQL 9.2.4. Currently our application works along with Oracle 11g. In Oracle schema we have created Synonyms. The context is this: In Oracle schema we have co

Re: [GENERAL] How to modify dump files created by pg_dump

no，I can't... Actually the dump file comes from another site... 在 2013-6-5，19:28，Raymond O'Donnell 写道： > On 05/06/2013 03:02, 任洪彩 wrote: >> Hi, >> Yes, you are right. >> But -d option not suitable for me. Because our flatform use -C option >> and -d template1 option to create the database desc

Re: [GENERAL] View's plan not taking advantage of WHERE?

Thanks Tom, I've found other discussion of this, that aggregates foul-up the planner with views. GROUP BY & DISTINCT don't work, we're trying to grab a subset of records and backfill any nulls to present a complete, single record... we're stuck with a view as this is used by a Rails app. We'll

Re: [GENERAL] View's plan not taking advantage of WHERE?

Mike Summers writes: > Other than the tests in the original post do you have any suggestions? If you're speaking of http://www.postgresql.org/message-id/CAJGeMG89QbDxMab7-aPD_yXVsGx7Q=auxym9ufvaq06crz4...@mail.gmail.com that has nothing to do with cached plans, obsolete or otherwise. You seem to

Re: [GENERAL] How to modify dump files created by pg_dump

On 05/06/2013 13:20, 私人邮箱 wrote: > no，I can't... > > Actually the dump file comes from another site... > > > 在 2013-6-5，19:28，Raymond O'Donnell 写道： > >> On 05/06/2013 03:02, 任洪彩 wrote: >>> Hi, >>> Yes, you are right. >>> But -d option not suitable for me. Because our flatform use -C option >>>

Re: [GENERAL] View's plan not taking advantage of WHERE?

Thanks Scott, interesting. Other than the tests in the original post do you have any suggestions? Thanks for your time.

Re: [GENERAL] View's plan not taking advantage of WHERE?

Note that the view DEFINITION is frozen, the query PLAN is NOT. -- 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] View's plan not taking advantage of WHERE?

On Wed, Jun 5, 2013 at 6:01 AM, Mike Summers wrote: > From what I'm reading the View is frozen when it's created, including it's > plan, and the usual solution is to use a set returning function... is this > not true? No it is not. Here: smarlowe=# create table a (id int); CREATE TABLE smarlowe

Re: [GENERAL] View's plan not taking advantage of WHERE?

>From what I'm reading the View is frozen when it's created, including it's plan, and the usual solution is to use a set returning function... is this not true? I've double checked all schemas and the view is only defined once. Thanks.

Re: [GENERAL] View's plan not taking advantage of WHERE?

On Wed, Jun 5, 2013 at 5:31 AM, Mike Summers wrote: > It appears that the culprit is a cached query plan, the tables in the UNION > have changed and no long match however the View does not throw a "each UNION > query must have the same number of columns" error. > > Is there a way to force the View

Re: [GENERAL] View's plan not taking advantage of WHERE?

It appears that the culprit is a cached query plan, the tables in the UNION have changed and no long match however the View does not throw a "each UNION query must have the same number of columns" error. Is there a way to force the View's query plan to be updated on each access?

Re: [GENERAL] How to modify dump files created by pg_dump

On 05/06/2013 03:02, 任洪彩 wrote: > Hi, > Yes, you are right. > But -d option not suitable for me. Because our flatform use -C option > and -d template1 option to create the database described in the dump > file to restore the dump file. > > The whole command like this: > # /opt/nokiasiemens/SS_Post

Re: [GENERAL] Streaming replication with sync slave, but disconnects due to missing WAL segments

On Wed, Jun 5, 2013 at 1:30 AM, Albe Laurenz wrote: > Jeff Janes wrote: > > On Tue, Jun 4, 2013 at 6:25 AM, > wrote: > >> I have a question about sync streaming replication. > >> > >> I have 2 postgresql 9.1 servers set up with streaming replication. On > the > >> master node the slave is configu

Re: [GENERAL] Synonyms in PostgreSQL 9.2.4

Panneerselvam Posangu, 05.06.2013 11:30: Currently our application works along with Oracle 11g. In Oracle schema we have created Synonyms. The context is this: In Oracle schema we have couple of users. They own tables, views, and other objects. We create synonyms for a given object ,grant needed

Re: [GENERAL] Synonyms in PostgreSQL 9.2.4

Panneerselvam Posangu wrote: > I am currently working on a conversion project. We plan to make our > application PostgreSQL compliant. > And for this we have chosen PosgreSQL 9.2.4. > > Currently our application works along with Oracle 11g. In Oracle schema we > have created Synonyms. The > cont

[GENERAL] Synonyms in PostgreSQL 9.2.4

Hi, I am currently working on a conversion project. We plan to make our application PostgreSQL compliant. And for this we have chosen PosgreSQL 9.2.4. Currently our application works along with Oracle 11g. In Oracle schema we have created Synonyms. The context is this: In Oracle schema we have co

Re: [GENERAL] Streaming replication with sync slave, but disconnects due to missing WAL segments

Jeff Janes wrote: > On Tue, Jun 4, 2013 at 6:25 AM, wrote: >> I have a question about sync streaming replication. >> >> I have 2 postgresql 9.1 servers set up with streaming replication. On the >> master node the slave is configured as a synchronous standby. I've verified >> that pg_stat_replicat