Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 16:11, Yogesh Sharma wrote: >>What you could do is copying its contents to a large disk, and then allow it to recover from the crash.  I will copy the PGDATA into large disk. After that it is require to execute some specific command or automatically recovery will start? If any

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 9/13/2017 9:11 PM, Yogesh Sharma wrote: >>What you could do is copying its contents to a large disk, and then allow it to recover from the crash.  I will copy the PGDATA into large disk. After that it is require to execute some specific command or automatically recovery will start? If any

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

>>What you could do is copying its contents to a large disk, and then allow it to recover from the crash. I will copy the PGDATA into large disk. After that it is require to execute some specific command or automatically recovery will start? If any command is require to execute please let me

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On Thu, Sep 14, 2017 at 12:44 PM, John R Pierce wrote: > prior to that error, something else catastrophic must have happened to the > system, that error is more of a side effect. recovering a database server > that far gone which is running such an obsolete version will

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

Dear all, As current situation, i can not upgrade on higher version. Is there any recovery command available? Regards, Yogesh On Thursday, September 14, 2017, Gavin Flower wrote: > On 14/09/17 15:29, Yogesh Sharma wrote: > >> Dear All, >> >> Thanks in advance.

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 9/13/2017 8:29 PM, Yogesh Sharma wrote: We are using Postgres 8.1.18 version. In Postgres log, we found below logs. –- CONTEXT:writing block 0 of relation 1664/0/1260 ERROR: could not write block 0 of relation 1664/0/1260: Bad address Due to this

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 15:29, Yogesh Sharma wrote: Dear All, Thanks in advance. We are using Postgres 8.1.18 version. In Postgres log, we found below logs. –- CONTEXT:writing block 0 of relation 1664/0/1260 ERROR: could not write block 0 of relation 1664/0/1260: Bad address

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

Dear All, Thanks in advance. We are using Postgres 8.1.18 version. In Postgres log, we found below logs. –- CONTEXT:writing block 0 of relation 1664/0/1260 ERROR: could not write block 0 of relation 1664/0/1260: Bad address Due to this pglog_Xlog

Re: [GENERAL] postgres db permissions

. Steve Pribyl From: pgsql-general-ow...@postgresql.org pgsql-general-ow...@postgresql.org on behalf of Joshua D. Drake j...@commandprompt.com Sent: Tuesday, June 2, 2015 12:44 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db permissions

Re: [GENERAL] postgres db permissions

: Tuesday, June 2, 2015 12:44 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db permissions On 06/02/2015 10:36 AM, Steve Pribyl wrote: Good Afternoon, Built a fresh 9.3. postgres server and added some users and noticed that any user can create tables in any database including

Re: [GENERAL] postgres db permissions

@postgresql.org Subject: Re: [GENERAL] postgres db permissions On 06/02/2015 10:50 AM, Steve Pribyl wrote: Josh, Via psql: CREATE ROLE bob LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT dbA TO bob; GRANT dbA_ro TO bob; GRANT dbB TO bob; GRANT dbB_ro TO bob; dbA, dbA_ro

Re: [GENERAL] postgres db permissions

On 06/02/2015 11:08 AM, Steve Pribyl wrote: They all look like this. CREATE ROLE dbA NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; And how are you connecting to the database via psql? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL

Re: [GENERAL] postgres db permissions

*From:* Melvin Davidson melvin6...@gmail.com *Sent:* Tuesday, June 2, 2015 12:55 PM *To:* Steve Pribyl *Cc:* Joshua D. Drake; pgsql-general@postgresql.org *Subject:* Re: [GENERAL] postgres db permissions Your problem is probably the INHERIT and GRANT dbA TO bob; GRANT dbA_ro TO bob; GRANT

Re: [GENERAL] postgres db permissions

From: Adrian Klaver adrian.kla...@aklaver.com Sent: Tuesday, June 2, 2015 1:20 PM To: Steve Pribyl; Melvin Davidson Cc: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db permissions On 06/02/2015 11:04 AM, Steve Pribyl wrote: None of the roles have permissions

Re: [GENERAL] postgres db permissions

As Tom advised, it's called a public schema for a reason. It means the general public (any user) has access to it and can create objects/tables in it. On Tue, Jun 2, 2015 at 2:58 PM, Joshua D. Drake j...@commandprompt.com wrote: On 06/02/2015 11:46 AM, Tom Lane wrote: Adrian Klaver

Re: [GENERAL] postgres db permissions

: [GENERAL] postgres db permissions On 06/02/2015 10:36 AM, Steve Pribyl wrote: Good Afternoon, Built a fresh 9.3. postgres server and added some users and noticed that any user can create tables in any database including the postgres database by default. Have I missed some step in securing

Re: [GENERAL] postgres db permissions

From: pgsql-general-ow...@postgresql.org pgsql-general-ow...@postgresql.org on behalf of Steve Pribyl steve.pri...@akunacapital.com Sent: Tuesday, June 2, 2015 1:45 PM To: Adrian Klaver; Melvin Davidson Cc: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db

Re: [GENERAL] postgres db permissions

: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db permissions Your problem is probably the INHERIT and GRANT dbA TO bob; GRANT dbA_ro TO bob; GRANT dbB TO bob; GRANT dbB_ro TO bob; options. If any of the dbA's have the permission to CREATE tables (and I suspect

Re: [GENERAL] postgres db permissions

On 06/02/2015 11:46 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@aklaver.com writes: On 06/02/2015 11:04 AM, Steve Pribyl wrote: I have noted that GRANT ALL ON SCHEMA public TO public is granted on postgres.schemas.public. I am looking at this in pgadmin so excuse my nomenclature. Is

Re: [GENERAL] postgres db permissions

, 2015 1:06 PM To: Steve Pribyl; Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres db permissions On 06/02/2015 10:50 AM, Steve Pribyl wrote: Josh, Via psql: CREATE ROLE bob LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT dbA

Re: [GENERAL] postgres db permissions

On 06/02/2015 11:04 AM, Steve Pribyl wrote: None of the roles have permissions on the postgres database. At this point they don't have any permissions on any databases. I have noted that GRANT ALL ON SCHEMA public TO public is granted on postgres.schemas.public. I am looking at this in

Re: [GENERAL] postgres db permissions

Adrian Klaver adrian.kla...@aklaver.com writes: On 06/02/2015 11:04 AM, Steve Pribyl wrote: I have noted that GRANT ALL ON SCHEMA public TO public is granted on postgres.schemas.public. I am looking at this in pgadmin so excuse my nomenclature. Is this what is allowing write access to the

Re: [GENERAL] postgres db permissions

On 06/02/2015 11:46 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@aklaver.com writes: On 06/02/2015 11:04 AM, Steve Pribyl wrote: I have noted that GRANT ALL ON SCHEMA public TO public is granted on postgres.schemas.public. I am looking at this in pgadmin so excuse my nomenclature. Is

[GENERAL] postgres db permissions

Good Afternoon, Built a fresh 9.3. postgres server and added some users and noticed that any user can create tables in any database including the postgres database by default. Have I missed some step in securing the default install? Steve Pribyl

Re: [GENERAL] postgres db permissions

On 06/02/2015 10:36 AM, Steve Pribyl wrote: Good Afternoon, Built a fresh 9.3. postgres server and added some users and noticed that any user can create tables in any database including the postgres database by default. Have I missed some step in securing the default install? How exactly

Re: [GENERAL] Postgres DB crashing

Alan Hodgson ahodg...@simkin.ca writes: 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

Re: [GENERAL] Postgres DB crashing

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.

Re: [GENERAL] Postgres DB crashing

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

Re: [GENERAL] Postgres DB crashing

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

Re: [pgadmin-support] [GENERAL] Postgres DB crashing

Thanks for the quick response. These errors are after disabling the autovacuum. auto_vacuum parameter was set to off. Can find the exact reason for this crash. Thanks and Regards Radha Krishna Date: Tue, 18 Jun 2013 13:54:09 -0400 Subject: Re: [pgadmin-support] [GENERAL] Postgres DB crashing

[GENERAL] Postgres DB crashing

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. Currently, the postgressql is

Re: [GENERAL] Postgres DB crashing

Stop the autovacuum process and try again. On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya udayabhanu1...@hotmail.comwrote: 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

Re: [GENERAL] Postgres DB crashing

On 6/18/2013 10:31 AM, bhanu udaya wrote: 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. Currently, the

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 2012-10-12, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote: On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote: We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 2012-10-12, Vishalakshi Navaneethakrishnan nvishalak...@sirahu.com wrote: --e0cb4efe29e2435cf104cbd3459b Content-Type: text/plain; charset=ISO-8859-1 Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1.

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On Sun, Oct 14, 2012 at 12:13:03AM +, Jasen Betts wrote: On 2012-10-12, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote: On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote: We have our production environment database server in

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan nvishalak...@sirahu.com wrote: Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 10/12/2012 08:05 AM, Amitabh Kant wrote: On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan nvishalak...@sirahu.com mailto:nvishalak...@sirahu.com wrote: Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote: On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote: We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan nvishalak...@sirahu.com wrote: Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more than

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

Hi all, While testing upgrade facility, I have installed postgres 9.2 using source package. Because it needs to be compiled with --disable-integer-datetimes. I have used this command ./configure --prefix=/opt/PostgreSQL/9.2/ --disable-integer-datetimes --without-readline After installation

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 10/12/12 9:52 PM, Vishalakshi Navaneethakrishnan wrote: While testing upgrade facility, I have installed postgres 9.2 using source package. Because it needs to be compiled with --disable-integer-datetimes. I have used this command ./configure --prefix=/opt/PostgreSQL/9.2/

[GENERAL] Postgres DB Migration from 8.3 to 9.1

Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other quick method to upgrade from 8.3 to 9.1. We need to reduce our downtime below 1

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 12/10/12 15:15, Vishalakshi Navaneethakrishnan wrote: Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other quick method to

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote: We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other quick method to upgrade from

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other quick method to upgrade from 8.3 to 9.1. We need to reduce our downtime below 1 hour.

[GENERAL] Postgres, DB design, and object IDs (of any ilk)

Just looking for postgres best practices input from the veterans: If the primary key of the customer table is cust_short_name and my DB reflects also customer departments, I can link a customer to its departments one of three ways: 1. The department table has a cust_short_name column and

Re: [GENERAL] Postgres, DB design, and object IDs (of any ilk)

On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton kentil...@gmail.com wrote: If the primary key of the customer table is cust_short_name and my DB reflects also customer departments, I can link a customer to its departments one of three ways: 1. The department table has a cust_short_name

Re: [GENERAL] Postgres, DB design, and object IDs (of any ilk)

I wouldn't trust OIDs, because they are 32bits for once. Secondly, Watch for index size. That's the main reason why (big)int as a key reference is a win over other types - at least in my general practice. And third advice, try different approaches, and queries - to figureout what would suit the

Re: [GENERAL] Postgres, DB design, and object IDs (of any ilk)

Merlin Moncure wrote: On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton kentil...@gmail.com wrote: If the primary key of the customer table is cust_short_name and my DB reflects also customer departments, I can link a customer to its departments one of three ways: 1. The department table has

Re: [GENERAL] Postgres, DB design, and object IDs (of any ilk)

kentil...@gmail.com (Kenneth Tilton) writes: Just looking for postgres best practices input from the veterans: OIDs are decidedly a bad idea; the difference between natural IDs and surrogate IDs is a general database issue that is fairly well documented in the literature and is not notably a

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

On Tue, 27 Dec 2005 23:25:37 +0200, Eugene [EMAIL PROTECTED] wrote: SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND ipto; If your [ipfrom, ipto] ranges are non-overlapping, you might be able to exploit that fact by adding something like ... ORDER BY ipfrom DESC LIMIT 1 Servus

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

On 2005-12-27, Eugene [EMAIL PROTECTED] wrote: I've indexed first two columns they are IPfrom, IPto also table is btree version of postgre is 7.4.8, on hosting I ask db like this SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND ipto; and get answer after 3-10 seconds, is there a

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

THanks for quick reply this is what i get now Index Scan using ipt on ipdb2 (cost=0.00..74265.76 rows=989636 width=118) (actual time=0.216..2379.608 rows=1 loops=1) Index Cond: (3229285376::bigint = ipto) Filter: (3229285376::bigint = ipfrom) Total runtime: 2379.666 ms (4 rows)

[GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

Hello! I've managed to import into postgre DB 3 800 000 rows of data (500 MB pure CSV ~ 2 GB SQL DB) It looks like this 69110784,69111807,US,UNITED STATES,ILLINOIS,BLOOMINGTON,40.4758,-88.9894,61701,LEVEL 3 COMMUNICATIONS INC,DSL-VERIZON.NET 69111808,69112831,US,UNITED

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote: I've indexed first two columns they are IPfrom, IPto also table is btree version of postgre is 7.4.8, on hosting You should ask them to upgrade; 7.4 is getting pretty old. I ask db like this SELECT * FROM ipdb2 WHERE '3229285376'

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

On 12/27/05, Eugene [EMAIL PROTECTED] wrote: Hello!I've managed to import into postgre DB 3 800 000 rows of data (500 MB pureCSV ~ 2 GB SQL DB)It looks like this69110784,69111807,US,UNITEDSTATES,ILLINOIS,BLOOMINGTON, 40.4758,-88.9894,61701,LEVEL 3COMMUNICATIONS

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote: I ask db like this SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND ipto; I'm pretty sure PostgreSQL won't be able to use any indexes for this (EXPLAIN ANALYZE would verify that).

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

-general@postgresql.org Subject: Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up? Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote: I ask db like this SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND ipto; I'm pretty sure PostgreSQL

Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

=?iso-8859-1?Q?Klein_Bal=E1zs?= [EMAIL PROTECTED] writes: Could you explain this a little bit more? What are the conditions of this situation that makes b-tree ineffective? Well, what he's trying to do is (abstracting a little) WHERE low_bound_col = probe_value AND probe_value =

[GENERAL] Postgres Db to XML/XML Schema Generator

Hi, I am working on a project where we have to define database tables and data in XML, have it validated by an XML Schemaand have it loaded into the database. We are using our EJBs to load the data into the database so I am not so concerned with that. What I am concerned with is generating

[GENERAL] postgres db failure

hi folks the postgres fail with follow mwssage pg_dump: dumpClasses(): SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 190/3F08779C is not satisfied --- flushed only to 190/3FD0 CONTEXT: writing block 268606 of relation 17142/17501 any clue? best

Re: [GENERAL] postgres db failure

marcelo Cortez wrote: hi folks the postgres fail with follow mwssage pg_dump: dumpClasses(): SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 190/3F08779C is not satisfied --- flushed only to 190/3FD0 CONTEXT: writing block 268606 of relation 17142/17501

Re: [GENERAL] Postgres DB

On Fri, 23 Apr 2004, Sumita Biswas wrote: Hi All, Our application is using the Postgres 7.3.4-RH database that is packaged with the AS3.0 for CCM. There is a variable type called refcursor that is being used by CAR Functions in Postgres database. This variable works fine when we execute