Re: [GENERAL] oracle_fdw
[I'll CC the oracle_fdw mailing list where this should be.] Rob wrote: > Environment: PostgreSQL 9.1.6 > SLES 11 SP2 > Oracle_fdw 0.9.7 > > I am trying to implement the use of oracle_fdw. > So far I have installed an oracle client on my postgres server. I can > connect to the oracle environment from the postgres server (as postgres os > user) by navigating to $ORACLE_HOME/bin then ./sqlplus /nolog > > then: > connect user@instance > > > I have downloaded and run make and make install for the oracle_fdw - both > executed successfully. I have created the extension successfully. I have > created a foreign server, foreign data wrapper and a foreign table. > > When i try a select from the foreign table I get: > > ERROR: error connecting to Oracle: OCIEnvCreate failed to create > environment handle > DETAIL: > > ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment > handle > SQL state: HV00N > > > from what limited info i can find this is most likely due to my ORACLE_HOME > environment variable or other environment setting? I have set ORACLE_HOME in > postgres user .bash_profile > > Where should i set ORACLE_HOME and TNS_ADMIN environment variables? Is there > something else I am missing? Does the database require to be restarted > following any changes to environment variables? I'd like to know some details about your Oracle installation: - Which product (Oracle server, Oracle client, Oracle Instant Client, ...)? - How was Oracle installed (OUI, RPM, zip)? - Which Oracle version? Also, can you tell me the "dbserver" option you used in CREATE SERVER? Now to your problem: If you can create the extension, the Oracle shared library can be loaded, so your library path is fine. That error is unfortunately a rather generic error that usually means that something in your environment isn't right. That may be ORACLE_HOME (if you don't use Instant Client), but could also be some more obscure environment variable. Can you send me the environment of the postmaster? Here's how to get it: In PostgreSQL: SELECT pg_backend_pid(); (Let's assume the result is 12345.) In a second session in the shell: ps -p12345 -oppid= (Let's assume the result is 23456.) As root or PostgreSQL OS user: cat /proc/23456/environ | xargs -0 -n1 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming Replication Server Crash
Hi All, We have configured Streaming Replication b/w Primary and Standby server and Pgpool-II load balancing module diverting SELECT statements to Standby server. As per our observations, Standby server crashed during peak hours on today and error message as follows: 2012-10-19 12:26:43 IST [11934]: [1-1] user=postgres,db=hmis LOG: duration: 20345.702 ms execute : SELECT DISTINCT y.ais_insm_name,y.ais_insm_type,ha_dism_name,ha_hudm_name,x2.ais_equip_receipt_details_value_lov, x3.ais_equip_receipt_details_value,x4.ais_equip_receipt_details_value, mast_frm_freq, t_report_code, t_report_date, t_report_month, t_report_year,t_report_createdate,t_report_date2, t_report_month2, t_report_year2,mast_frm_validity_days FROM TNHSPTOOL_FORM_MASTER, TRAN_712_MASTERfull join ais_institution_master as y on( t_instiution_code = y.ais_insm_code) full join ha_district_master on( t_district_code=ha_dism_code ) full join ha_hud_master on( t_hud_code=ha_hudm_code )full join tran_712_664 x2 on (x2.t_rep_code=t_report_code and x2.t_rep_rowno=3) full join tran_712_664 x3 on (x3.t_rep_code=t_report_code and x3.t_rep_rowno=5) full join tran_712_664 x4 on (x4.t_rep_code=t_report_code and x4.t_rep_rowno=36) WHERE mast_frm_code = T_REPORT_FRMID AND y.ais_insm_code ='00231' and T_REPORT_FRMID =712 and T_REPORT_OLDFLAG='O' ORDER BY T_REPORT_CODE DESC 2012-10-19 12:26:46 IST [1338]: [18-1] user=,db= LOG: server process (PID 15565) was terminated by signal 10 2012-10-19 12:26:46 IST [1338]: [19-1] user=,db= LOG: terminating any other active server processes 2012-10-19 12:26:46 IST [18450]: [3-1] user=postgres,db=DBHMS WARNING: terminating connection because of crash of another server process Standby Server configuration Details as follows: > max_wal_senders = 5 > wal_sender_delay = 200ms > wal_keep_segments = 128 > vacuum_defer_cleanup_age = 0 > hot_standby = on > max_standby_archive_delay = -1 > max_standby_streaming_delay = -1 PostgreSQL version: 9.0.4 OS: Solaris 64bit Could you please share your inputs,to fix this issue. Thanks & Regards, Raghu Ram
Re: [GENERAL] Improve MMO Game Performance
ok, thanks i will look into walbuffers asynchronous_commit=off is a doubt for responsive environment (pg manual). for ex. would it help if, a game player , has to perform next move on basis of his/her previous move plus other players move. all is sent to table and for each player a particular set is retrieved with a minimalistic data portfolio > Date: Thu, 18 Oct 2012 11:24:00 -0300 > From: alvhe...@2ndquadrant.com > To: laurenz.a...@wien.gv.at > CC: arvin...@hotmail.com; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Improve MMO Game Performance > > Albe Laurenz wrote: > > Arvind Singh wrote: > > > Are there any particular settings or methods available to improve Just > > insert_table operations > > > > The obvious ones: > > - Wrap several of them in a transaction. > > - Increase checkpoint_segments. > > - Set fsync=off and hope you don't crash. > > I think it would work to set asynchronous_commit=off for the > transactions that insert moves. That way, the fsync flushes happen in > the background and are batched. Raising wal_buffers is probably a good > idea, and keep an eye on how the walwriter is doing. > > -- > Álvaro Herrera http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
Re: [GENERAL] Help estimating database and WAL size
On 2012-10-15, Daniel Serodio (lists) wrote: >> >> OID is optional, IIRC PGXID is not > I hadn't heard of PGXID, I've just searched Google but found no > reference to this term except for this e-mail thread and some source > code. What is PGXID? Where can I learn more about hit? That was the wrong name there's some sort of ID associated with the row it's part of the fixed per-row overhead, it may vary between different versions of the row. -- ⚂⚃ 100% natural -- 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] Improve MMO Game Performance
Hello Arvind, i worked on MMO (and especially their performance) some time ago. I really like(d) them. :) we are all aware of the popular trend of MMO games. where players face To understand my query, please consider the following scenario /we store game progress in a postgres table./ A tournament starts with four players and following activity 1. Each player starts with 100hitpoints 2. player 1 makes a strike (we refer to a chart to convert blows to hitpoints with random-range %) 3. player 2 has 92HP, and returns a light blow, so player1 has 98hp That is a very simple description or a very simple system. The above two round will now be in Game Progress Table, as |ROWPlayer1HP Player2HP Strikefrom StrikeTo ReturnStrikeHP Round TimeStamp StrikeMethod 1 100100 0 0 0 0 298 92 P1P22 1 | At this point you should reconsider if you need this much data. It is faster to store less data. For example you do not need the colum "ReturnStrikeHP". You can calculate the damage from the difference between the rows. If there is a cyclic change of the attacker (P1->P2->P1->P2->P1->P2 ...) you just need to store the starting player and could drop the "strik from" and "strike to" column. If you need it and there are just 2 players in one fight, reduce the column to "attacker" and store if it is player one or two. The one which is not in the column is the defender. Also why do you store each round? Most time there are just reports, charts or animations about the fight. You can generate them in a more compact form and just store 1 row with this information. In this way you will reduce the number of needed INSERT-operations a lot. And you will speed-up the SELECT because less data must be read. I hope this will help you. Greetings from Germany, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index creation problem
Hi, I'm getting a problem where my application hangs in the process of adding a field to a table. The app adds the field, then creates an index on the field. It hangs for ages (minutes) until I cancel the query. My investigation so far has been Look at current queries: agilebasedata=# SELECT datname,procpid,current_query FROM pg_stat_activity; datname | procpid | current_query -+-+- agilebaseschema |5799 | in transaction agilebasedata | 18126 | SELECT datname,procpid,current_query FROM pg_stat_activity; agilebasedata |5844 | agilebasedata |5108 | CREATE INDEX l_ntvs1fk9de719830100m5aoi8suwo ON ntvs1fk9desoci59z(lower(m5aoi8suwo4jocu76) varchar_pattern_ops) agilebasedata |5109 | in transaction agilebaseschema | 25200 | agilebasedata | 29257 | agilebasedata | 31574 | (8 rows) As you can see, the only user query running is the CREATE INDEX. Cancelling this with select pg_cancel_backend(5108); gets the app back on it's feet. I thought there may be some sort of deadlock, but there is only one long running query. By the way, the indexing query should return quickly, as there are 0 rows in the table. It does in fact return immediately when you run it manually: agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops); ERROR: relation "l_nx4uaurg3r1981190097whsqcun3e9" already exists agilebasedata=# drop index l_nx4uaurg3r1981190097whsqcun3e9; DROP INDEX agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops); CREATE INDEX One thing to mention is that there are a lot of indexes and relations: agilebasedata=# select count(*) from pg_index; count --- 2587 (1 row) agilebasedata=# select count(*) from pg_class; count --- 5361 (1 row) I wonder if I'm running up against some sort of limit. I am going to change the code so it doesn't add an index (it's not always necessary) but would like to get to the bottom of things first. Regards Oliver Kohll www.gtwm.co.uk - company / www.agilebase.co.uk - product
Re: [GENERAL] Index creation problem
On 19 Oct 2012, at 13:28, Frank Lanitz wrote: > Just an idea without bigger investigation: Whare are the idle in > transactions are doing? Maybe they are blocking the create index. > > Cheers, > Frank Good question, I don't know. The app runs on Java / Apache Tomcat, which maintains a connection pool, so I assumed the IDLEs were just opened connections but I didn't notice two were idle in transaction. I haven't dealt with this before but this looks like a good explanation: http://www.depesz.com/2008/08/28/hunting-idle-in-transactions/ I will up logging and try to replicate. Next time I will look at pg_locks too. Also, the PG version is 9.1.6. Oliver
Re: [GENERAL] Index creation problem
Oliver Kohll wrote: > I'm getting a problem where my application hangs in the process of adding a field to a table. The app > adds the field, then creates an index on the field. It hangs for ages (minutes) until I cancel the > query. My investigation so far has been > > Look at current queries: > > agilebasedata=# SELECT datname,procpid,current_query FROM pg_stat_activity; > datname | procpid | current_query > -+-+ -- > --- > agilebaseschema |5799 | in transaction > agilebasedata | 18126 | SELECT datname,procpid,current_query FROM pg_stat_activity; > agilebasedata |5844 | > agilebasedata |5108 | CREATE INDEX l_ntvs1fk9de719830100m5aoi8suwo ON > ntvs1fk9desoci59z(lower(m5aoi8suwo4jocu76) varchar_pattern_ops) > agilebasedata |5109 | in transaction > agilebaseschema | 25200 | > agilebasedata | 29257 | > agilebasedata | 31574 | > (8 rows) > > As you can see, the only user query running is the CREATE INDEX. Cancelling this with > > select pg_cancel_backend(5108); > > gets the app back on it's feet. > > I thought there may be some sort of deadlock, but there is only one long running query. By the way, > the indexing query should return quickly, as there are 0 rows in the table. It does in fact return > immediately when you run it manually: > > agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON > nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops); > ERROR: relation "l_nx4uaurg3r1981190097whsqcun3e9" already exists > agilebasedata=# drop index l_nx4uaurg3r1981190097whsqcun3e9; > DROP INDEX > agilebasedata=# CREATE INDEX l_nx4uaurg3r1981190097whsqcun3e9 ON > nx4uaurg3r94vwqhj(lower(whsqcun3e9lfzlwlv) varchar_pattern_ops); > CREATE INDEX > > > One thing to mention is that there are a lot of indexes and relations: > > agilebasedata=# select count(*) from pg_index; > count > --- > 2587 > (1 row) > > agilebasedata=# select count(*) from pg_class; > count > --- > 5361 > (1 row) > > I wonder if I'm running up against some sort of limit. I am going to change the code so it doesn't add > an index (it's not always necessary) but would like to get to the bottom of things first. In the example you show, some connections are "idle in transaction". Such connections can hold locks that block your CREATE INDEX. Could you look at pg_locks if there is a lock that is not granted? There could also be prepared transactions holding locks, if you use that feature. There is also CREATE INDEX CONCURRENTLY which locks the table less. Does that make a difference? Does the process for the hanging backend consume CPU? You could try taking a stack trace to see where it hangs. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multiple Cluster on same host
I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok with local connections, I cannot connect using any of my tools to the second database with port 5433, including pgAdmin. Please help. Any parameter that I need to modify for the new database with port 5433? |netstat -an | grep 5433|shows, |tcp 0 0 0.0.0.0:5433 0.0.0.0:* LISTEN tcp6 0 0 :::5433 :::* LISTEN unix 2 [ ACC ] STREAM LISTENING 72842 /var/run/postgresql/.s.PGSQL.5433 | |iptables -L|shows, |Chain INPUT (policy ACCEPT) target prot opt source destination Chain FORWARD (policy ACCEPT) target prot opt source destination Chain OUTPUT (policy ACCEPT) target prot opt source destination|
Re: [GENERAL] Multiple Cluster on same host
On 10/19/2012 07:02 AM, GMAIL wrote: > I have 2 cluster databases, running on the same host, Ubuntu. My fist > database port is set to default but my second database port is set to > 5433 in the postgresql.conf file. While everything is ok with local > connections, I cannot connect using any of my tools to the second > database with port 5433, including pgAdmin. Please help. Any parameter > that I need to modify for the new database with port 5433? Is pg_hba.conf for the second cluster set up to allow access from wherever you are connecting from? - Chris -- 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] Multiple Cluster on same host
Il 19/10/2012 15:24, Chris Ernst ha scritto: On 10/19/2012 07:02 AM, GMAIL wrote: I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok with local connections, I cannot connect using any of my tools to the second database with port 5433, including pgAdmin. Please help. Any parameter that I need to modify for the new database with port 5433? Is pg_hba.conf for the second cluster set up to allow access from wherever you are connecting from? - Chris yes -- 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] Multiple Cluster on same host
On 10/19/2012 07:24 AM, GMAIL wrote: > Il 19/10/2012 15:24, Chris Ernst ha scritto: >> On 10/19/2012 07:02 AM, GMAIL wrote: >>> I have 2 cluster databases, running on the same host, Ubuntu. My fist >>> database port is set to default but my second database port is set to >>> 5433 in the postgresql.conf file. While everything is ok with local >>> connections, I cannot connect using any of my tools to the second >>> database with port 5433, including pgAdmin. Please help. Any parameter >>> that I need to modify for the new database with port 5433? >> Is pg_hba.conf for the second cluster set up to allow access from >> wherever you are connecting from? > yes What happens when you try to connect? Any error message? Log entries? - Chris -- 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] Help estimating database and WAL size
Am 2012-10-15 23:13, schrieb John R Pierce: On 10/15/12 2:03 PM, Daniel Serodio (lists) wrote: John R Pierce wrote: On 10/08/12 1:39 PM, Daniel Serodio (lists) wrote: 3) Estimate the size of the transaction log ** We've got no idea how to estimate this, need advice ** postgres doesn't have a 'transaction log', it has the WAL (Write-Ahead Logs). These are typically 16MB each. on databases with a really heavy write load, I might bump the checkpoint_segments as high as 60, which seems to result in about 120 of them being created, 2GB total. these files get reused, unless you are archiving them to implement a continuous realtime backup system (which enables "PITR", Point in Time Recovery) Thanks, I was using the term "transaction log" as a synonym for WAL. We're planning on enabling PITR; how can we calculate the WAL size and the WAL archive size in this case? its based on how much data you're writing to the database. Wheen you write tuples (rows) to the database, they are stored in 8K pages/blocks which are written to the current WAL file as they are committed, when that WAL file fills up, or the checkpoint_timeout is reached (the default is 30 seconds, I believe) , the WAL file is written to the archive. To be able to utilize PITR, you need a complete base backup of the file system, and /all/ the archived WAL files since that base backup was taken. In huge number of cases you will also write these files to some kind of network storage via e.g. CIFS or NFS so you have access to them via your warm-standby-machines. I want to say: this is taken some storage but can be reviewed kind of independent from database itself. Cheers, Frank -- 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] Multiple Cluster on same host
Il 19/10/2012 15:40, Chris Ernst ha scritto: On 10/19/2012 07:24 AM, GMAIL wrote: What happens when you try to connect? Any error message? Log entries? - Chris now i be able to connect, after update pgAdmin, but i receive the following error: "ERROR: column "datconfig" does not exist LINE 1: ...b.dattablespace AS spcoid, spcname, datallowconn, datconfig,..." "Column not found in pgSet:rolconfig" -- 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] Multiple Cluster on same host
On 10/19/2012 07:50 AM, GMAIL wrote: > Il 19/10/2012 15:40, Chris Ernst ha scritto: >> On 10/19/2012 07:24 AM, GMAIL wrote: >> What happens when you try to connect? Any error message? Log entries? >> - Chris > now i be able to connect, after update pgAdmin, but i receive the > following error: > "ERROR: column "datconfig" does not exist > LINE 1: ...b.dattablespace AS spcoid, spcname, datallowconn, datconfig,..." > > "Column not found in pgSet:rolconfig" You didn't mention the versions of either PostgreSQL or pgAndmin, but that sounds like you're using a newer version of PostgreSQL (9.1.x ?) with and older version on pgAdmin (< 1.12.x ?). You may simply need to upgrade pgAdmin (>= 1.12.x). - Chris -- 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] Multiple Cluster on same host
Il 19/10/2012 16:05, Chris Ernst ha scritto: On 10/19/2012 07:50 AM, GMAIL wrote: Il 19/10/2012 15:40, Chris Ernst ha scritto: On 10/19/2012 07:24 AM, GMAIL wrote: What happens when you try to connect? Any error message? Log entries? - Chris now i be able to connect, after update pgAdmin, but i receive the following error: "ERROR: column "datconfig" does not exist LINE 1: ...b.dattablespace AS spcoid, spcname, datallowconn, datconfig,..." "Column not found in pgSet:rolconfig" You didn't mention the versions of either PostgreSQL or pgAndmin, but that sounds like you're using a newer version of PostgreSQL (9.1.x ?) with and older version on pgAdmin (< 1.12.x ?). You may simply need to upgrade pgAdmin (>= 1.12.x). - Chris i use postgresql 9.1 and pgadmin 1.8.4 -- 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] Index creation problem
Oliver Kohll wrote: >> In the example you show, some connections are "idle in transaction". >> Such connections can hold locks that block your CREATE INDEX. >> Could you look at pg_locks if there is a lock that is not granted? >> There could also be prepared transactions holding locks, if >> you use that feature. > > Yes there was indeed an un-granted lock on the table. By logging all SQL I think I've found the client > code which is causing the problem, a line was added recently that basically causes > > DROP INDEX IF EXISTS > followed by > CREATE INDEX > > to be run twice in quick succession. These are all in the same thread and transaction so I wouldn't > have thought there'd be a locking problem but it's the most likely candidate at the moment. You should always include the list in your replies - other people might be interested in the solution. There must be at least two transactions involved to create a locking problem like you describe. But since CREATE INDEX takes strong locks, it can easily get locked by other "harmless" things. Yours, Laurenz Albe -- 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] Multiple Cluster on same host
postgresql 9.1 and pgadmin 1.8.4 And there it is. You'll need pgAdmin >= 1.12.x to work with PostgreSQL 9.1. - Chris can be a firewall problem? -- 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] Multiple Cluster on same host
Il 19/10/2012 15:40, Chris Ernst ha scritto: On 10/19/2012 07:24 AM, GMAIL wrote: Il 19/10/2012 15:24, Chris Ernst ha scritto: On 10/19/2012 07:02 AM, GMAIL wrote: I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok with local connections, I cannot connect using any of my tools to the second database with port 5433, including pgAdmin. Please help. Any parameter that I need to modify for the new database with port 5433? Is pg_hba.conf for the second cluster set up to allow access from wherever you are connecting from? yes What happens when you try to connect? Any error message? Log entries? - Chris sorry I was confused. i don't receive any message -- 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] Multiple Cluster on same host
Il 19/10/2012 16:11, Shaun Thomas ha scritto: On 10/19/2012 09:06 AM, GMAIL wrote: i use postgresql 9.1 and pgadmin 1.8.4 Yeah, that version of pgAdmin is *way* too old. 1.16 was released fairly recently, and it works great. i try the version 1.16 but it doesn't work. i don't get any error message -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Somewhat automated method of cleaning table of corrupt records for pg_dump
Hey! I'm currently in the situation that due to (probably) broken memory in a server, I have a corrupted PostgreSQL database. Getting at the data that's in the DB is not time-critical (because backups have restored the largest part of it), but I'd still like to restore what can be restored from the existing database to fill in the remaining data. VACUUM FULL runs successfully (i.e., I've fixed the blocks with broken block headers, removed rows that have invalid OIDs as recorded by the VACUUM, etc.), but dumping the DB from the rescue system (which is PostgreSQL 8.3.21) to transfer it to another still fails with "invalid memory alloc request size 18446744073709551613", i.e., most probably one of the TEXT colums in the respective tables contains invalid sizings. The methods for restoring from this condition - which I've not really found much info on (except some posts on pgsql-hackers) - all require loads of manual work (and don't seem to actually work in my corruption case, I can "select * from offset limit 1" without it failing on me); is there any possibility to automate the row-checking? I'm not proficient with PL/pgSQL, but would start trying to write a respective script in case it's not available somewhere. I've found a corresponding PL/pgSQL method on the web, but that does _not_ work for my case (it's 9.0+ only). Thanks in advance if anybody could point me at a corresponding hint! -- --- Heiko. -- 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] Multiple Cluster on same host
Il 19/10/2012 16:33, Shaun Thomas ha scritto: If you don't get any error message from the client or the server, are you sure it didn't work? I only ask because 1.8 was so old, I'm surprised it even worked with the version you had before. The catalog has changed a lot since then, and the error you reported is pretty much exactly what you'd expect by using an old pgAdmin with a new Postgres due to missing entries. You might think about deleting the pgAdmin entry and re-entering it from scratch to make sure all the settings are as you expect. There should be some kind of hint from the database log, too. if i connect with pgadmin from the host where i've created the two clusters it does work but if i connect from another pc, with pgadmin 1.16 or other software, to the host with postgresql i get a "connection refused" message -- 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] Improve MMO Game Performance
Arvind Singh wrote: > > ok, > > thanks i will look into walbuffers > > asynchronous_commit=off is a doubt for responsive environment (pg manual). > for ex. > > would it help if, > > a game player , has to perform next move on basis of his/her previous move > plus other players move. > all is sent to table and for each player a particular set is retrieved with a > minimalistic data portfolio The database will see all data as committed, regardless of commit being synchronous or asynchronous (note the setting is actually called synchronous_commit). There is only a doubt about it being committed after a database crash. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Multiple Cluster on same host
Il 19/10/2012 16:55, Shaun Thomas ha scritto: Yep. You've got a firewall between you and your server. Somewhere. Good luck tracking that down, sir. Everything should clear up when/if that gets resolved. i think that the problem should be the service iptables. what i have to change if i want to accept all request to the port 5433? -- 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] Multiple Cluster on same host
On 10/19/2012 08:06 AM, GMAIL wrote: > Il 19/10/2012 16:05, Chris Ernst ha scritto: >> On 10/19/2012 07:50 AM, GMAIL wrote: >>> Il 19/10/2012 15:40, Chris Ernst ha scritto: On 10/19/2012 07:24 AM, GMAIL wrote: What happens when you try to connect? Any error message? Log entries? - Chris >>> now i be able to connect, after update pgAdmin, but i receive the >>> following error: >>> "ERROR: column "datconfig" does not exist >>> LINE 1: ...b.dattablespace AS spcoid, spcname, datallowconn, >>> datconfig,..." >>> >>> "Column not found in pgSet:rolconfig" >> You didn't mention the versions of either PostgreSQL or pgAndmin, but >> that sounds like you're using a newer version of PostgreSQL (9.1.x ?) >> with and older version on pgAdmin (< 1.12.x ?). You may simply need to >> upgrade pgAdmin (>= 1.12.x). >> > i use postgresql 9.1 and pgadmin 1.8.4 And there it is. You'll need pgAdmin >= 1.12.x to work with PostgreSQL 9.1. - Chris -- 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] oracle_fdw
Thanks Laurenz for your post...
Some more info
Oracle Server:Oracle 11g R2 (11.2.0.2.0)
Client: 11.2
Was installed using Oracle Universal Installer
I don't really want to post the full environment of the postmaster but
basically I could see no entry in there for ORACLE_HOME or TNS_ADMIN, should
I?
LD_LIBRARY_PATH=mypostgreshomedirectory/lib
are there any others in particular of interest?
Here is my fdw, server and foreign table specs. I have 'myinstancename'
defined in tnsnames.ora which is in $ORACLE_HOME/NETWORK/ADMIN
CREATE FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator;
ALTER FOREIGN DATA WRAPPER oracle_fdw
OWNER TO postgres;
CREATE SERVER myinstancename
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'myinstancename');
ALTER SERVER myinstancename
OWNER TO postgres;
CREATE FOREIGN TABLE public.wild_lek
("WL_ID" integer ,
"WL_ALIAS" character varying(50) ,
"WL_AHM_FL" character varying(1) ,
"WL_INACTIVE_FL" character varying(1) ,
"WL_SATELLITE_FL" character varying(20) ,
"WL_LESPPSG_FL" character varying(1) )
SERVER myinstancename
OPTIONS (table 'MYUSER.MYTABLE');
ALTER FOREIGN TABLE 'MYUSER.MYTABLE' OWNER TO postgres;
Rob
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/oracle-fdw-tp5728931p5729005.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] Multiple Cluster on same host
On 10/19/2012 09:06 AM, GMAIL wrote: i use postgresql 9.1 and pgadmin 1.8.4 Yeah, that version of pgAdmin is *way* too old. 1.16 was released fairly recently, and it works great. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Multiple Cluster on same host
Il 19/10/2012 16:45, Shaun Thomas ha scritto: On 10/19/2012 09:38 AM, GMAIL wrote: but if i connect from another pc, with pgadmin 1.16 or other software, to the host with postgresql i get a "connection refused" message Ok. That narrows it down slightly. If you check your PG logs for FATAL messages, and you don't see anything complaining about pg_hba.conf not having an entry for your host, it's probably a firewall issue. For reference, the firewall can be anywhere between you and your host. Try to ping the host and port where your PG server is running. If you can't even get that far, I'd inquire to your Infrastructure department. using ping i receive a connection timed out -- 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] Multiple Cluster on same host
On 10/19/2012 09:14 AM, GMAIL wrote: i try the version 1.16 but it doesn't work. i don't get any error message If you don't get any error message from the client or the server, are you sure it didn't work? I only ask because 1.8 was so old, I'm surprised it even worked with the version you had before. The catalog has changed a lot since then, and the error you reported is pretty much exactly what you'd expect by using an old pgAdmin with a new Postgres due to missing entries. You might think about deleting the pgAdmin entry and re-entering it from scratch to make sure all the settings are as you expect. There should be some kind of hint from the database log, too. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Multiple Cluster on same host
On 10/19/2012 09:59 AM, GMAIL wrote: i think that the problem should be the service iptables. what i have to change if i want to accept all request to the port 5433? iptables only affects the machine your server is running on. There can be a firewall literally *anywhere* between your client and the host server. You may need to search farther than the local server, especially since I think you copied your firewall settings in the first message, and they were all empty. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Index creation problem
On 19 Oct 2012, at 15:09, "Albe Laurenz" wrote: > You should always include the list in your replies - > other people might be interested in the solution. Oops, thought I had. > > There must be at least two transactions involved > to create a locking problem like you describe. > But since CREATE INDEX takes strong locks, it can > easily get locked by other "harmless" things. OK, I've reduced the calls to CREATE INDEX, waiting to test that. If there's still an issue I will use CONCURRENTLY as you suggested. Many thanks Oliver
Re: [GENERAL] Multiple Cluster on same host
On 19/10/2012 15:38, GMAIL wrote: > Il 19/10/2012 16:33, Shaun Thomas ha scritto: >> >> If you don't get any error message from the client or the server, are >> you sure it didn't work? >> >> I only ask because 1.8 was so old, I'm surprised it even worked with >> the version you had before. The catalog has changed a lot since then, >> and the error you reported is pretty much exactly what you'd expect by >> using an old pgAdmin with a new Postgres due to missing entries. >> >> You might think about deleting the pgAdmin entry and re-entering it >> from scratch to make sure all the settings are as you expect. There >> should be some kind of hint from the database log, too. > if i connect with pgadmin from the host where i've created the two > clusters it does work > but if i connect from another pc, with pgadmin 1.16 or other software, > to the host with postgresql i get a "connection refused" message Is the server actually listening on any interface other than localhost? What's in the listen_addresses entry in postgresql.conf for the cluster on port 5433? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Multiple Cluster on same host
On 10/19/2012 09:52 AM, GMAIL wrote: using ping i receive a connection timed out Yep. You've got a firewall between you and your server. Somewhere. Good luck tracking that down, sir. Everything should clear up when/if that gets resolved. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Multiple Cluster on same host
On 10/19/2012 09:38 AM, GMAIL wrote: but if i connect from another pc, with pgadmin 1.16 or other software, to the host with postgresql i get a "connection refused" message Ok. That narrows it down slightly. If you check your PG logs for FATAL messages, and you don't see anything complaining about pg_hba.conf not having an entry for your host, it's probably a firewall issue. For reference, the firewall can be anywhere between you and your host. Try to ping the host and port where your PG server is running. If you can't even get that far, I'd inquire to your Infrastructure department. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgwatch: installs but doesn't display anything
Hi all,
trying to run pgwatch1.0 on Ubuntu 12.04 through a Bitnami LAPPstack.
# select version();
PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5), 32-bit
Pgwatch installation finishes OK but the webpages of pgwatch display nothing.
Overview: has a table that says "No records found"
Dashboard: No items to display
etc.
pgwatch user was created:
Role name | Attributes | Member of
---++---
pgwatch | Superuser, Replication | {}
Also there is a pgwatch database (no tables, just 40 dblink functions)
owned by role pgwatch:
Name | Owner | Encoding | Collate |Ctype|
Access privileges
---+--+--+-+-+---
pgwatch | pgwatch | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Nothing generated in the pg logs when refreshing the webpages.
Any ideas?
TIA,
Thalis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
