Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On 9/6/2016 8:21 PM, Venkata B Nagothi wrote: it's a full copy from the production, so it's 2.3TB Thats quite huge. pg_upgrade would be a better choice and yes, downtime is needed. You need to have the database shutdown all through the upgrade process. How long it will take depends on

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On Wed, Sep 7, 2016 at 10:43 AM, Patrick B wrote: > >> how large is the full database cluster? >> >> if its only a few GB or whatever, and not grossly complex, pg_dumpall -h >> oldhost | psql -h newhost, is the simplest way to move a complete set of >> databases from an

Re: [GENERAL] Materialized view auto refresh

Dear Harry. I'm sorry. Please check it again at http://it.ued.udn.vn/myprojects/pgTriggerGen/: http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v2_src.rar . Best regards, On Tue, Sep 6, 2016 at 2:42 PM, hari.prasath wrote: > Dear Nguyen Tran

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > how large is the full database cluster? > > if its only a few GB or whatever, and not grossly complex, pg_dumpall -h > oldhost | psql -h newhost, is the simplest way to move a complete set of > databases from an old server to a new. if there are large tables with > millions of rows indexed,

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On 9/6/2016 4:47 PM, Patrick B wrote: 1 - upgrade master server 2 - by performing #1 - the slaves would become unused (streaming replication wouldn't work) 3 - Setup new slave running Postgres 9.4 4 - dump the db to the new server how large is the full database cluster? if its only a few

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > John, > Yes, Rackspace supports slony. I used to work there and they use slony on > their core system. > > Patrick, > You cannot stream directly from 9.2 to 9.4. However, you can just create a > separate 9.4 cluster (on a separate port). > Then pg_dump from 9.2 in plain format and it will

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On Tue, Sep 6, 2016 at 7:28 PM, Patrick B wrote: > > > 2016-09-07 11:25 GMT+12:00 John R Pierce : > >> On 9/6/2016 4:20 PM, Melvin Davidson wrote: >> >>> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on >>> the slave. >>> >>

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-07 11:25 GMT+12:00 John R Pierce : > On 9/6/2016 4:20 PM, Melvin Davidson wrote: > >> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on >> the slave. >> > > does rackspace support slony? how about amazon dms ? > > slony requires configuring

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On 9/6/2016 4:20 PM, Melvin Davidson wrote: If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on the slave. does rackspace support slony? how about amazon dms ? slony requires configuring replication on each table. if the database has a large complex schema this could

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on the slave. On Tue, Sep 6, 2016 at 6:43 PM, John R Pierce wrote: > On 9/6/2016 3:27 PM, Rich Shepard wrote: > > On Wed, 7 Sep 2016, Patrick B wrote: > > 2 - I've never done a Postgres upgrade before,

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On 9/6/2016 3:27 PM, Rich Shepard wrote: On Wed, 7 Sep 2016, Patrick B wrote: 2 - I've never done a Postgres upgrade before, can you give some guide here? Is usually a easy thing? How long can it take? Is a downtime needed? I suggest the place to start is 'man pg_upgrade.' It is helpful.

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On Wed, 7 Sep 2016, Patrick B wrote: 2 - I've never done a Postgres upgrade before, can you give some guide here? Is usually a easy thing? How long can it take? Is a downtime needed? Patrick, I suggest the place to start is 'man pg_upgrade.' It is helpful. Rich -- Sent via pgsql-general

[GENERAL] Postgres UPGRADE from 9.2 to 9.4

Hi guys, I'll be migrating my Postgres 9.2 database from Rackspace to Amazon. To do that work, I'll be using DMS at amazon... unfortunately DMS needs a Postgres 9.4+ version at least ( http://docs.aws.amazon.com/pt_br/dms/latest/userguide/CHAP_Introduction.Sources.html) - So that means that I'll

Re: [GENERAL] Privileges on public schema can't be revoked?

Stephen Frost writes: > \dn+ in psql will give you the access privileges for all schemas. > I'd have to look at the "other solutions" you're referring to, but, in > general, we do not exclude the public role in any way from the access > privilege system. Possibly Greg was

Re: [GENERAL] Privileges on public schema can't be revoked?

Gregm * Greg Fodor (gfo...@gmail.com) wrote: > A, I wasn't aware of the PUBLIC meta-role. Not sure if it's useful > feedback, I spent a lot of time digging around the web for solutions > that would basically let me query the database to see all of the > effective privileges for a user, and

Re: [GENERAL] Privileges on public schema can't be revoked?

A, I wasn't aware of the PUBLIC meta-role. Not sure if it's useful feedback, I spent a lot of time digging around the web for solutions that would basically let me query the database to see all of the effective privileges for a user, and none of the solutions I found were able to get me to a

Re: [GENERAL] pgAdmin 4 records limit of 2000

On Tue, Sep 6, 2016 at 3:26 PM, Adrian Klaver wrote: > On 09/06/2016 10:05 AM, dudedoe01 wrote: > >> Hi, >> >> I have pgAdmin 9.4. I can only view 2000 rows of data at a time in >> pgAdmin 4 >> while the previous version 9.3 there was no limit. Is there anyway to >>

[GENERAL] Thanks to the Pg community (Louisiana Flood)

Hello fellow community members, I just wanted to put out a public thanks to all the community members that sent donations to the CMD office in Baton Rouge. We received a ton of needed supplies. You can see the reference here: http://www.linuxhiker.org/2016/08/helping-lousiana.html Thanks

Re: [GENERAL] PostgreSQL Database performance

On Tue, Sep 6, 2016 at 1:18 PM, Steve Atkins wrote: > >> On Sep 6, 2016, at 12:08 PM, Scott Marlowe wrote: >> >> On Fri, Sep 2, 2016 at 9:38 PM, Pradeep wrote: >>> >>> max_connections = 100 >>> shared_buffers = 512MB >>>

Re: [GENERAL] PostgreSQL Database performance

On Fri, Sep 2, 2016 at 8:38 PM, Pradeep wrote: > Dear Team, > > > > Could you please help me, after changing the below parameters in > PostgreSQL configuration file it was not reflecting in OS level and also > Database performance is degrading. > What were they

Re: [GENERAL] pgAdmin 4 records limit of 2000

On 09/06/2016 10:05 AM, dudedoe01 wrote: Hi, I have pgAdmin 9.4. I can only view 2000 rows of data at a time in pgAdmin 4 while the previous version 9.3 there was no limit. Is there anyway to remove the limit on the rows of data I can see all the data concurrently. Please provide insights.

Re: [GENERAL] PostgreSQL Database performance

> On Sep 6, 2016, at 12:08 PM, Scott Marlowe wrote: > > On Fri, Sep 2, 2016 at 9:38 PM, Pradeep wrote: >> >> max_connections = 100 >> shared_buffers = 512MB >> effective_cache_size = 24GB >> work_mem = 110100kB > > This is WAY too high for

Re: [GENERAL] PostgreSQL Database performance

On Fri, Sep 2, 2016 at 9:38 PM, Pradeep wrote: > Dear Team, > > > > Could you please help me, after changing the below parameters in PostgreSQL > configuration file it was not reflecting in OS level and also Database > performance is degrading. > > > > Example: I am

Re: [GENERAL] Help with slow query - Pgsql 9.2

On Mon, Sep 5, 2016 at 6:53 PM, Patrick B wrote: > Hi guys, > > I got this query: > >> SELECT id,jobid,description,serialised_data >> FROM logtable >> WHERE log_type = 45 >> AND clientid = 24011 >> ORDER BY gtime desc > > What is really going to help you here is

Re: [GENERAL] PostgreSQL Database performance

On Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the operating system cache more instead. The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB. --- Warm Regards, --

Re: [GENERAL] Privileges on public schema can't be revoked?

Greg Fodor writes: > Apologies in advance about this since it is likely something obvious, > but I am seeing some very basic behavior that does not make sense. > I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to > see if it was a regression.) After creating a

Re: [GENERAL] Privileges on public schema can't be revoked?

Greg, * Greg Fodor (gfo...@gmail.com) wrote: > Apologies in advance about this since it is likely something obvious, > but I am seeing some very basic behavior that does not make sense. > I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to > see if it was a regression.) After

Re: [GENERAL] PostgreSQL Database performance

Hi. "shared_buffers" should be set to 30-40% of your system RAM. This param controls how much memory database may use. Please see https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html Ilya Kazakevich JetBrains

Re: [GENERAL] PostgreSQL Database performance

Which version of PostgreSQL are you using on your windows? Increasing work_mem can lead to far less disk-swapping, and therefore far quicker queries. However, it can cause problems if set too high, and should be constrained taking into account max_connections. The following calculation is what is

[GENERAL] Help with slow query - Pgsql 9.2

Hi guys, I got this query: > SELECT id,jobid,description,serialised_data > FROM logtable > WHERE log_type = 45 > AND clientid = 24011 > ORDER BY gtime desc Explain analyze: https://explain.depesz.com/s/XKtU So it seems the very slow part is into: -> Bitmap Index Scan on

Re: [GENERAL] IDE for function/stored proc development.

I do use Dbwrench, but is pretty basic, no fancy procedures / function development support. Enviado do meu smartphone Sony Xperia™ Pavel Stehule escreveu Hi 2016-09-03 11:36 GMT+02:00 Tim Uckun : > Does anybody use an IDE for doing heavy duty stored proc

[GENERAL] pgAdmin 4 records limit of 2000

Hi, I have pgAdmin 9.4. I can only view 2000 rows of data at a time in pgAdmin 4 while the previous version 9.3 there was no limit. Is there anyway to remove the limit on the rows of data I can see all the data concurrently. Please provide insights. Thanks in Advance,

[GENERAL] Privileges on public schema can't be revoked?

Apologies in advance about this since it is likely something obvious, but I am seeing some very basic behavior that does not make sense. I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to see if it was a regression.) After creating a test database, and a test user that I revoke

Re: [GENERAL] IDE for function/stored proc development.

Martijn Tonies (Upscene Productions) escreveu > Good morning, > > >I looked at your purchase, and did not see any Postgres version. Am I > missing (/misunderstanding) something here? > > It’s not yet available, please wait until the end of the week > > That being said, the

[GENERAL] PostgreSQL Database performance

Dear Team, Could you please help me, after changing the below parameters in PostgreSQL configuration file it was not reflecting in OS level and also Database performance is degrading. Example: I am using Windows 2008 R2 server .For PostgreSQL I have allocated 24GB RAM out of 32GB. However

Re: [GENERAL] pgadmin4 rc1 query tool performance

> Am 06.09.2016 um 15:23 schrieb Adrian Klaver : > > On 09/06/2016 02:35 AM, Attila Soki wrote: >> Hi, >> >> i testing the latest release of pgadmin4 (rc1) and noticed that the query >> tool is significantly slower than the query tool in pgadmin3. >> i am not sure if

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

On Tue, Aug 30, 2016 at 1:10 PM, Nicolas Grilly wrote: > We are developing a multitenant application which is currently based on > MySQL, but we're thinking of migrating to PostgreSQL. > > We rely on clustered indexes to preserve data locality for each tenant. >

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On Sun, Sep 4, 2016 at 4:37 PM, Patrick B wrote: > That's great news! My only concern is about the "RSYNC" - Hope that doesn't > take long!!! > > This all steps must be performed by me on the next few days/weeks - I'll > keep you guys updated... Keen to see the new DB

Re: [GENERAL] Check if there 6 last records of same type without gaps

Sandor, this has worked, thank you - On Tue, Sep 6, 2016 at 3:35 PM, Sándor Daku wrote: > > Of course you need the played field you relied on it in the order by > clause. You can use the result of a select in a from clause of another > select. > > SELECT SUM(skips) from

Re: [GENERAL] Check if there 6 last records of same type without gaps

On 6 September 2016 at 15:19, Alexander Farber wrote: > Hello Charles and other, please excuse my stupidity, but - > > On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher < > clavadetsc...@swisspug.org> wrote: > >> >> You must group by played, as the message

Re: [GENERAL] Check if there 6 last records of same type without gaps

Also tried the second suggestion: words=> select count(action='skip') from words_moves where gid=3 group by played order by played desc limit 6; count --- 1 1 1 1 1 1 (6 rows)

Re: [GENERAL] pgadmin4 rc1 query tool performance

On 09/06/2016 02:35 AM, Attila Soki wrote: Hi, i testing the latest release of pgadmin4 (rc1) and noticed that the query tool is significantly slower than the query tool in pgadmin3. i am not sure if this occurs only on my computer or only under os x (10.10.5) or is this a known behavior. I

Re: [GENERAL] Check if there 6 last records of same type without gaps

Hello Charles and other, please excuse my stupidity, but - On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher < clavadetsc...@swisspug.org> wrote: > > You must group by played, as the message suggests. You are implicitly > selecting the column through order by, although you don't have it in

Re: [GENERAL] Check if there 6 last records of same type without gaps

Hello > On 06.09.2016, at 14:35, Alexander Farber wrote: > > No, I am sorry - for struggling with probably basic questions, but without > GROUP BY I get another error: > > org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must > appear in the

Re: [GENERAL] Check if there 6 last records of same type without gaps

No, I am sorry - for struggling with probably basic questions, but without GROUP BY I get another error: org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must appear in the GROUP BY clause or be used in an aggregate function| Where: PL/pgSQL function

Re: [GENERAL] Check if there 6 last records of same type without gaps

On 6 September 2016 at 14:23, Alexander Farber wrote: > Thank you, Sandor - > > On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku wrote: > >> >> Get the last 6 record and >> >> 1. ... action='SKIP' as isskip ... then you can group on and count the

Re: [GENERAL] Check if there 6 last records of same type without gaps

Thank you, Sandor - On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku wrote: > > Get the last 6 record and > > 1. ... action='SKIP' as isskip ... then you can group on and count the > skip moves. If there is 6 of them the game ends. > > 2. ... sum(case when action='SKIP' then

Re: [GENERAL] Check if there 6 last records of same type without gaps

On 6 September 2016 at 12:32, Alexander Farber wrote: > Good afternoon, > > for a 2-player game I store moves in the following 9.5.4 table: > > CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign'); > > CREATE TABLE words_moves ( > mid SERIAL

[GENERAL] Check if there 6 last records of same type without gaps

Good afternoon, for a 2-player game I store moves in the following 9.5.4 table: CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign'); CREATE TABLE words_moves ( mid SERIAL PRIMARY KEY, action words_action NOT NULL, gid integer NOT NULL REFERENCES

[GENERAL] pgadmin4 rc1 query tool performance

Hi, i testing the latest release of pgadmin4 (rc1) and noticed that the query tool is significantly slower than the query tool in pgadmin3. i am not sure if this occurs only on my computer or only under os x (10.10.5) or is this a known behavior. how to repeat: create table test1 (a int, t

Re: [GENERAL] postgres driver for mysql

> From: Mimiko >To: Posthresql-general >Sent: Monday, 5 September 2016, 19:38 >Subject: [GENERAL] postgres driver for mysql > > >Hello to all. > >I want to move applications to postgres. But there are applications >which can use only mysql or

Re: [GENERAL] Restricted access on DataBases

Dear Everybody! I'm sorry because lack of answer - I try to do it now. 2016-09-05 16:19 GMT+02:00 Adrian Klaver : > On 09/05/2016 05:45 AM, Durumdara wrote: > >> Dear PG-masters! >> >> We want to put more databases to one server, to "public" schema: >> DB_A, DB_B,

Re: [GENERAL] Materialized view auto refresh

Dear Nguyen Tran Quoc Vinh Source link is broken. Please check this http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_src.rar cheers - Harry On Mon, 05 Sep 2016 11:50:49 +0530 Nguyễn Trần Quốc Vinh ntquocv...@gmail.comwrote Dear Harry. You