Re: [GENERAL] invalid OID warning after disk failure
On Mon, Nov 17, 2014 at 10:20 AM, Gabriel Sánchez Martínez < gabrielesanc...@gmail.com> wrote: > > The situation appears to be stable now, but upon running REINDEX and > VACUUM on one of the databases, I get the following: > > WARNING: relation "pg_attrdef" TID 1/1: OID is invalid > WARNING: relation "pg_attrdef" TID 1/2: OID is invalid > WARNING: relation "pg_attrdef" TID 1/3: OID is invalid > ... > > Should I drop the database and restore it from a backup? My most recent > backup is from late September, so I would lose some data. I also backed up > what I could as soon as the disks started giving errors, but I don't know > if I can trust that. > > Should I drop the entire cluster? > > > are you receiving any kind of error messages,while taking database dump with "pg_dump" utility. If you are not receiving any kind of error message,try to take a database dump and restore database dump file in another database server and perform all sanity checks. If all sanity checks are working fine,you can create a new cluster and restore all database dump's. Thanks & Regards Raghu Ram
Re: [GENERAL] Yosemite (OSX 10.0) problems with Postgresql
On Mon, Oct 20, 2014 at 5:56 PM, Jerry Levan wrote: > 2) SystemStarter is no longer available. Gulp… I am not a very good plist > creator. > Is there a fairly generic plist I can edit to specify my locations of the > software bits so I can have postgresql started at boot time? > ## Enable PostgreSQL to auto start in Mac OS X 10.7.x (Lion) sudo launchctl load -w /Library/LaunchDaemons/com.edb.launchd.postgresql-9.1.plist NOTE: "com.edb.launchd.postgresql-9.1.plist" file contains PostgreSQL Data directory and PostgreSQL binary locations. ## Manually Start PostgreSQL ## su as user "postgres" and run server [sudo su - postgres]: cd /Library/PostgreSQL/9.1/bin/ ./pg_ctl -D /Library/PostgreSQL/9.1/data/ start ## Manually Stop PostgreSQL ./pg_ctl -D /Library/PostgreSQL/9.1/data/ stop Thanks & Regards Raghu Ram
Re: [GENERAL] POWA tool
On Tue, Aug 26, 2014 at 3:11 PM, Ramesh T wrote: > i downloaded But where i need to unzip powa-REL_1_1.zip i'm using putty > tool remote server.. > > > -bash-4.1$ unzip powa-REL_1_1.zip > -bash: unzip: command not found > > You need to Install "unzip" utility. Once you installed "unzip" utility,you can extract in "/tmp" directory. for Debian and Ubuntu: 1 apt-get install unzip for Red Hat Linux/Fedora/CentOS users: 1 yum install unzip
Re: [GENERAL] POWA tool
-x 6 root root 4096 Aug 19 2014 templates > -rw-r--r-- 1 root root4 Aug 19 2014 VERSION [root@localhost ui]# cp powa.conf-dist powa.conf > [root@localhost ~]# /usr/bin/curl get.mojolicio.us | sh > % Total% Received % Xferd Average Speed TimeTime Time > Current > Dload Upload Total SpentLeft > Speed > 0540540 0179 0 --:--:-- --:--:-- --:--:-- > 184 > % Total% Received % Xferd Average Speed TimeTime Time > Current > Dload Upload Total SpentLeft > Speed > 100 262k 100 262k0 0 70788 0 0:00:03 0:00:03 --:--:-- > 120k > --> Working on Mojolicious > Fetching http://www.cpan.org/authors/id/S/SR/SRI/Mojolicious-5.33.tar.gz > ... OK > Configuring Mojolicious-5.33 ... OK > Building Mojolicious-5.33 ... OK > Successfully installed Mojolicious-5.33 > 1 distribution installed [root@localhost ui]# morbo script/powa > [Tue Aug 26 00:46:29 2014] [debug] Reading configuration file > "/tmp/powa-REL_1_1/ui/powa.conf". > [Tue Aug 26 00:46:29 2014] [info] Listening at "http://*:3000";. > Server available at http://127.0.0.1:3000. *Step 7: *Run http://127.0.0.1:3000 in web browser. Thanks & Regards Raghu Ram
Re: [GENERAL] POWA tool
On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell wrote: > On 20/08/2014 16:41, Ramesh T wrote: > > Hello, > > > > when i ran following query, > > postgres=# SELECT * FROM pg_stat_statements; > > > > > > ERROR: relation "pg_stat_statements" does not exist > > LINE 1: SELECT * FROM pg_stat_statements; > > > > > > i need to install POWA..i got powa.zip > > please let me know how to install POWA.ZIP for my postgres using putty > > tool .. > are you referring below Tool ? PoWA is PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graph to help monitor and tune your PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW. http://www.postgresql.org/about/news/1537/ Thanks & Regards Raghu Ram
Re: [GENERAL] Documents/ppts/pdfs
On Sat, Nov 9, 2013 at 12:53 PM, Jayadevan M wrote: > Hi, > > When I search for material on PostgreSQL, once in a while I see reasonably > old, but very good content in file such as > http://www.postgresql.org/files/developer/transactions.pdf > How can I browse to a top level folder ( > http://www.postgresql.org/files/developer) and see what else is available > ? > I can't find any links in postgresql.org or documentation pointing to > files like this. > > These presentations are available in http://www.postgresql.org/developer/coding/ Presentations - Transaction Processing in PostgreSQL<http://www.postgresql.org/files/developer/transactions.pdf> (From OSDN Oct 2000) - A Tour of PostgreSQL Internals<http://www.postgresql.org/files/developer/tour.pdf> (From OSDN Oct 2000) - History of PostgreSQL Open-Source Development<http://www.postgresql.org/files/developer/history.pdf> (From OSDN Oct 2000) - Database Internals Presentation<http://www.postgresql.org/files/developer/internalpics.pdf> (From 2001) - PostgreSQL Concurrency Issues<http://www.postgresql.org/files/developer/concurrency.pdf> (From OSCON 2002) - Recent PostgreSQL Optimizer Improvements (in 7.4)<http://www.postgresql.org/files/developer/optimizer.pdf> (From OSCON 2003) Thanks & Regards Raghu Ram
Re: [GENERAL] pg_dumpall from a script
On Tue, Oct 22, 2013 at 10:50 AM, James Sewell wrote: > That looks great, but it doesn't really help with my problem unless I'm > missing something (very possible!) > > I need a way to backup either from SQL in PSQL (possibly \!) or from a > PG/PLSQL function to a file with a name set from a :variable. > > This would be triggered by a certain action in the database (applying a > patch). > > Hope that's a bit clearer! > > > You can use a stored procedure with this plsh http://plsh.projects.postgresql.org/ , like this: CREATE FUNCTION dump_db(text, text) RETURNS text AS ' #!/bin/sh pg_dump $1 > $2 ' LANGUAGE plsh; Note that you must CREATE LANGUAGE first, $1 is db_name, $2 is file name and check for write permissions of $2. Thanks & Regards Raghu Ram
Re: [GENERAL] pg_dumpall from a script
On Tue, Oct 22, 2013 at 8:07 AM, James Sewell wrote: > Hello, > > I need to trigger a database dump from a SQL script (or function, but I > think that is even less likely). > > I know I can do: > > \! pg_dumpall > /mydir/myfile > > Which is fine, but I need to use a variable to set the name. > > \set myfile 'filename' > \! pg_dumpall > /mydir/:myfile > > Doesn't seem to work. > > Any ideas? > Below URL provides more information of Automated database Backup's on Linux: http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux Thanks & Regards Raghu Ram
Re: [GENERAL] postgresql.conf error
On Fri, Oct 18, 2013 at 2:01 PM, Jayadevan M wrote: > Thanks. This is what I have. May be it is not really an error? > > 2013-10-18 12:23:54.996 IST,,,8855,,523c23ea.2297,20,,2013-09-20 16:01:06 > IST,,0,LOG,0,"received SIGHUP, reloading configuration files","" > 2013-10-18 12:23:54.996 IST,,,8855,,523c23ea.2297,21,,2013-09-20 16:01:06 > IST,,0,LOG,55P02,"parameter ""superuser_reserved_connections"" cannot be > changed without restarting the server","" > 2013-10-18 12:23:54.997 IST,,,8855,,523c23ea.2297,22,,2013-09-20 16:01:06 > IST,,0,LOG,F,"configuration file > ""/pgdata/prod/data_93/postgresql.conf"" contains errors; unaffected > changes were applied","" > To effect new changes related to "superuser_reserved_connections" parameters in Postgresql.conf file requires RESTART of the PostgreSQL Service. Thanks & Regards Raghu Ram
Re: [GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?
On Mon, Oct 7, 2013 at 8:32 PM, sunpeng wrote: > Hi, Friends, are there any ETL tools (free or commercial) available for > PostgreSQL? > > *ETL Tools for PostgreSQL::* Definition: An ETL process data to load into the database from a flat file A. Extract B. Transform C. Load 1. PGLoader - Load .csv file 2. Benetl 1.8 - Load .txt or .csv or .xls file 3. Talend Open Studio => http://www.talend.com/index.php 4. Pentaho Kettle => http://kettle.pentaho.com/ 5. Visual Importer => Data can be imported from flat files,Excel.MS Access,Oracle.Mysql.Interbase,PostgreSQL,oleDB,ODBC and DBF files. 6. CloverETL => http://en.wikipedia.org/wiki/CloverETL Both [3 &4] of these ETL solutions have a lot of the same types of features: - Both are Java based and run on Linux and Windows (Talend is Eclipse-based) - Visual designers for creating the transformations - Connectivity for a myriad of databases, including all the big DBs, text formats, etc. - Supports distributing jobs across multiple servers if you are doing serious lifting - Excellent error handling and error notification systems - Active and helpful forums (Kettle is older and seems to have a larger community however) - Free and open source - They are complex enough to handle a lot of tasks, but not so much as to kill you - There are versions of both (GeoKettle and Spatial Data Integrator) that are tailored for GIS Thanks & Regards Raghu Ram
Re: [GENERAL] Need some basic information
On Wed, Aug 14, 2013 at 11:10 AM, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > Hi all, > > I am new in this group and need some help from your side. > > We have a mediation product which is initially using Oracle as database. > > Some of our customer interested to move Postgres 9.1. > > Our mediation product storing some configuration related information in > data base and some type of logging data. > > We are using Hibernate in Java to interact with Postgres 9.1. > > Can you please suggest some test cases or some issues which may hamper us? > > You can get more information with below Link: http://zetcode.com/db/postgresqljavatutorial/ Thanks & Regards Raghu Ram
Re: [GENERAL] Trigger to update table info
On Mon, Aug 12, 2013 at 12:46 PM, Arvind Singh wrote: > Hello friends, > > I have a table with the following structure > > > Create table postablestatus > > ( > > tablename varchar(30) NOT NULL, > > updatetime timestamp, > > reccount int, > > size int, > >CONSTRAINT postablestatus_pkey PRIMARY KEY(tablename) > > ) WITH (oids = false); > > > where > a) tablename - is the name of table where a record is inserted,updated or > deleted > b) updatetime - the time when table was updated (w/o timezone) > c) reccount - records in table > d) size - bytesize of the table > > -- > for example, if a table called item_master had 1 record and a > new record is inserted and it's size is 2000 bytes > > > postablestatus must have a entry like > > item_master08/12/2013 12:40:00 2 2000 > -- > > I request to know how to write a trigger in PLSQL so that the > postablestatus > is updated whenever a record is inserted,updated or deleted in item_master > > PLEASE NOTE : a new record must be created if the item_master is not > present in postablestatus > > > You will get more information from below link for creation of audit trigger: http://wiki.postgresql.org/wiki/Audit_trigger Thanks & Regards Raghu Ram
Re: [GENERAL] Migration from Symfoware to PostgreSQL-Constructor functions
> how to see list of system tables, functions, view and user defined tables, > functions, views. > > You can get more details from below URL: Extracting META information from PostgreSQL (INFORMATION_SCHEMA)<http://www.alberton.info/postgresql_meta_info.html> Thanks & Regards Raghu Ram
Re: [GENERAL] Fwd: corrupted files
On Tue, Jul 30, 2013 at 4:07 AM, Klaus Ita wrote: > Sorry for cross-posting, i read that pg-bug was not the right place for > this email > > Hi list! > > depressed me gets error messages like these: > > 2013-07-29 20:57:09 UTC ERROR: could not access > status of transaction 8393477 > 2013-07-29 20:57:09 UTC DETAIL: Could not open > file "pg_clog/0008": No such file or directory. > > combined with the error output of queries that do not work. > > I looked in pg_clog and correct, 0008 is missing. > > > You can recreate a missed "pg_clog" file with below command: dd if=/dev/zero of=~/9.1/main/pg_clog/0008 bs=256k count=1 (To make the uncommitted record as they haven't been committed.) and then try to start the cluster. Thanks & Regards Raghu Ram
Re: [GENERAL] new instance of postgres on windows
On Thu, Jul 18, 2013 at 3:52 PM, Arvind Singh wrote: > Hello and greetings, > > > I want to install postgresql for use as the backend to a Windows > application. > > > This seems to be no problem if postgresql is NOT already installed on the > system. which is not in this case. > > > > postgresql is already installed and unless the command line parameters > contain the superpassword etc of the existing installation , the install > fails. As I will likely never know the superpassword or other account > details of any pre-existing postgresql instances and the machine owners may > not either. > > > How to then proceed and install a new instance that can be used by our > application. > > > Window 7 > Postgres 9.3 beta > > > > > > The password you need to reset is at the operating system level. To do this on Windows, do the following: 1) Right click on the 'My Computer' desktop icon ( or if the icon is not on the desktop, open the start menu and right click on the 'My Computer' menu item) 2) Choose 'Manage' from the pop up menu 3) Expand the 'Local Users and Groups' folder 4) Expand the 'Users' folder 5) On the right hand side of the screen, you will see all the users defined on the system. 6) You can right click on the "postgres" user name and choose 'Set Password' and then change it to something else OR You can delete the user and then have the installer recreate the user. You will need to be logged into the computer as an administrative user to do the above. Thanks & Regards Raghu Ram
Re: [GENERAL] How can I perform client-only installation from source code on Windows?
On Wed, Apr 3, 2013 at 3:48 AM, MauMau wrote: > Hello, > > Is there any way to perform client-only installation from source code on > Windows? On UNIX/Linux, client-only installation is described here: > > > http://www.postgresql.org/**docs/current/static/install-**procedure.html<http://www.postgresql.org/docs/current/static/install-procedure.html> > > [Excerpt] > Client-only installation: > If you want to install only the client applications and interface > libraries, then you can use these commands: > gmake -C src/bin install > gmake -C src/include install > gmake -C src/interfaces install > gmake -C doc install > > > > Any information is appreciated. > You can download PostgreSQL binaries for windows operating system from below link: http://www.enterprisedb.com/products-services-training/pgbindownload Thanks & Regards, Raghu Ram SkypeID: raghu.ramedb
Re: [GENERAL] w7 vs linux
On Fri, Nov 23, 2012 at 3:09 PM, Peter Kroon wrote: > Is pgsql faster on linux? > Currently I've made an installation on W7 and the converted queries are > about 3 times slower then on mssql. > There's still some optimization to do tho...but the current results don't > look to good. > Below URL provides more information on this topic: http://serverfault.com/questions/222430/is-postgresql-suited-to-one-os-is-it-better-on-linux-than-windows -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] create table in memory
On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon wrote: > I've converted some mssql functions and they appear to be slower in pgsql. > I use a lot of declared tables in mssql as they are created in memory. > Which makes it very fast. > > > > 2012/11/23 Peter Kroon > >> Is a temp table created to memory(ram) or disk? >> I've converted some msssq >> > > While the temporary table is in-use, For a small table the data will be in the memory, For a large table if data is not fit in memory then data will be flushed to disk periodically as the database engine needs more working space for other requests. A permanent table persist after terminating PostgreSQL session, whereas temporary table is automatically destroyed when PostgreSQL session ends. The memory is controlled by temp_buffers parameter (postgresql.conf) -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] Restore postgres to specific time
On Fri, Nov 23, 2012 at 8:59 AM, wd wrote: > Thanks for your reply, the logs are something like bellow，postgres will > restore every wal log I put in the xlog directory，and then continues > waiting for next wal log. The postgres version is 9.1.6. > > [2012-11-22 18:49:24.175 CST 25744 50ae0334.6490 1 0]LOG: database > system was shut down in recovery at 2012-11-22 18:49:22 CST > [2012-11-22 18:49:24.176 CST 25744 50ae0334.6490 2 0]LOG: entering > standby mode > [2012-11-22 18:49:24.210 CST 25744 50ae0334.6490 3 0]LOG: restored > log file "000100280023" from archive > [2012-11-22 18:49:24.213 CST 25744 50ae0334.6490 4 0]LOG: redo starts > at 28/23001900 > [2012-11-22 18:49:24.619 CST 25744 50ae0334.6490 5 0]LOG: consistent > recovery state reached at 28/23C8 > [2012-11-22 18:49:24.620 CST 25736 50ae0333.6488 2 0]LOG: database > system is ready to accept read only connections > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280024': No such file or > directory > [2012-11-22 18:50:49.828 CST 25744 50ae0334.6490 6 0]LOG: restored > log file "000100280024" from archive > [2012-11-22 18:50:50.291 CST 25744 50ae0334.6490 7 0]LOG: restored > log file "000100280025" from archive > [2012-11-22 18:50:50.759 CST 25744 50ae0334.6490 8 0]LOG: restored > log file "000100280026" from archive > [2012-11-22 18:50:51.220 CST 25744 50ae0334.6490 9 0]LOG: restored > log file "000100280027" from archive > [2012-11-22 18:50:51.686 CST 25744 50ae0334.6490 10 0]LOG: restored > log file "000100280028" from archive > [2012-11-22 18:50:52.145 CST 25744 50ae0334.6490 11 0]LOG: restored > log file "000100280029" from archive > [2012-11-22 18:50:52.613 CST 25744 50ae0334.6490 12 0]LOG: restored > log file "00010028002A" from archive > [2012-11-22 18:50:53.073 CST 25744 50ae0334.6490 13 0]LOG: restored > log file "00010028002B" from archive > [2012-11-22 18:50:53.538 CST 25744 50ae0334.6490 14 0]LOG: restored > log file "00010028002C" from archive > [2012-11-22 18:50:53.999 CST 25744 50ae0334.6490 15 0]LOG: restored > log file "00010028002D" from archive > [2012-11-22 18:50:54.459 CST 25744 50ae0334.6490 16 0]LOG: restored > log file "00010028002E" from archive > [2012-11-22 18:50:54.920 CST 25744 50ae0334.6490 17 0]LOG: restored > log file "000000010028002F" from archive > cp: cannot stat `/export/t/xlog/000100280030': No such file or > directory > cp: cannot stat `/export/t/xlog/000100280030': No such file or > directory > > > > As per logs,it is working as a host-standby. For doing the point in time recovery,recovery.conf file requires only two settings: restore_command='cp /t/xlog/%f %p' recovery_target_time='2012-11-22 5:01:09 CST' --Raghu Ram
Re: [GENERAL] Postgresql - 8.3 Replication in windows
On Fri, Nov 2, 2012 at 1:03 PM, Vishalakshi Navaneethakrishnan < nvishalak...@sirahu.com> wrote: > Hi all, > > Can you please tell me , how to set up replication in win7, without slony > tool.. > > Below URL provides a configuration steps of Slony Replication on Windows Operating System: http://www.mokisystems.com/blog/setting-up-slony-on-windows/ Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
[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] Again, problem with pgbouncer
On Mon, Oct 1, 2012 at 3:56 PM, Phoenix Kiula wrote: > Hi, > > - PG 9.0.10 > - Pgbouncer version 1.4.2 > > Not long ago, during the last server reboot for us, we had fixed the > really painful (and largely mysterious) process of setting up > pgbouncer. > > File permissions and other mysteries were solved with help from > Raghavendra: > http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854 > > After a long we rebooted our server today and again, as if on cue, > pgbouncer has problems yet again :( > > PG itself is running without problems. > > The Pgbouncer process starts properly too. All the auth file, log file > etc are setup as mentioned in that URL above. We haven't changed > anything at all! > > At first, just connecting via pgbouncer port was giving the "no user" > error. Which is funny, because the authfile has been working without > problems forever. The .pgpass file had the same problems, and is still > the same all this time. > > So, upon reading that old thread again, I guessed that the "postgres" > user permissions were needed, so I did this: > > chown -R postgres:postgres /etc/pgbouncer > chown -R postgres:postgres /var/run/pgbouncer/ > chown postgres:postgres /var/log/pgbouncer.log > chown postgres:postgres /var/lib/pgsql/pgbouncer.txt > > Then restarted both PG and Pgbouncer. > > Now pgbouncer won't do anything at all. Trying to connect to psql via > the pgbouncer port gives this error: > > psql: could not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.6789"? > > > And in the log is this line: > > > 2012-10-01 06:12:00.703 3754 FATAL @src/main.c:553 in function > write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied > [13] > > > What now? Would appreciate some pointers. > > Thanks. > > Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have "postgres" user permissions,please assign it and then start the pgbouncer. Looking to the error thrown by pgbouncer the port shown up as 6789, but whereas the link of pgbouncer.ini file it has 6389. Please mention appropriate port while connecting via pgbouncer and give port number which is in pgbouncer.ini file. -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation skypeid: raghu.ramedb Blog:http://raghurc.blogspot.in/
Re: [GENERAL] Determining a table column by the view column.
On Tue, Aug 14, 2012 at 7:48 PM, Dmitriy Igrishin wrote: > Hey all, > > Is there way to determine a table column referenced by > a view column via SQL? > > I want to create an universal function to determine > mandatoriness of some column of the view (i.e. > check the not null constraint of underlying table column). > > Below query will find out the list of columns those have rules or views ? SELECT d1.refobjid::regclass AS table, a.attname AS column, d2.refobjid::regclass AS "referenced by view" FROM pg_depend d1 JOIN pg_depend d2 ON (d1.objid = d2.objid AND d1.classid = 'pg_rewrite'::regclass AND d1.refclassid ='pg_class'::regclass AND d2.classid = 'pg_rewrite'::regclass AND d2.refclassid = 'pg_class'::regclass AND d2.deptype='i') JOIN pg_attribute a ON (d1.refobjid = a.attrelid AND d1.refobjsubid = a.attnum) WHERE d1.refobjid=*'base_table'*::regclass ORDER BY 1, 2, 3; -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] Problem running "ALTER TABLE...", ALTER TABLE waiting
On Wed, Jul 18, 2012 at 1:24 AM, Brian McNally wrote: > Hello, > > I'm running Postgres 9.0.2 on RHEL 5.6 x86_64 and trying to run the > following statement: > > alter table samples add column in_esp1234 text; > > When I do this the command just hangs. ps output indicates that it's > waiting: > > postgres: postgres exomeSNP [local] ALTER TABLE waiting > > When I look to see what might have that table locked I don't see anything: > > select * from pg_locks where relation=(select oid from pg_class where > relname='samples'); > > I have a nearly identical server where this command works. It seems like > something has this table locked, but I haven't been able to find it. Any > ideas? > Please find more information using below query: \x SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.current_query AS waiting_query, waiting.mode AS waiting_mode, waiting.pidAS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.current_queryAS other_query, other.mode AS other_mode, other.pid AS other_pid, other.granted AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.procpid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid ) WHERE NOT waiting.granted AND waiting.pid <> other.pid; -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.
On Mon, Jul 16, 2012 at 5:44 PM, Olga Vingurt wrote: > Hi, > > ** ** > > We are using PostgreSQL 8.3 on Windows Server. > > The db crashed and now it fails to start up. > > ** ** > > The error is: > > FATAL: could not access status of transaction 4020264 > > DETAIL: Could not read from file "pg_clog/0003" at offset 212992: No > error. > > ** ** > > Additional log from event viewer: > > LOG: database system was interrupted; last known up at 2012-07-12 > 11:35:00 CEST > > ** ** > > ** ** > > How can we recover from the current state? > > ** ** > > > Please perform below steps: 1. Backup the current pg_clog/0003 file in different directory 2. Create a file by assumption of make the uncommitted record as they haven't been committed. command as follows: *dd if=/dev/zero of=/pg_clog/0003 bs=256K count=1 * 3. Start the Cluster -- Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] ERROR: out of shared memory - But the table is empty
On Thu, Jul 12, 2012 at 8:23 PM, Stefan Schwarzer wrote: > Hi there, > > not being an expert for Postgres…. I have a Postgis table with the > countries of the world. Now, I would like to drop it. I got the error > message: > > ERROR: out of shared memory > HINT: You might need to increase max_locks_per_transaction. > > and thought that it would be easier to drop first all rows, and then drop > the empty table. > > But it seems that it has a problem with dropping the index: > > DROP INDEX gis.countries_uid; > > actually generates that error message. > > So, do I need to dig now into the postgres config file? It seems strange > to me that Postgres has a problem deleting an empty table, though. > > The shared lock table has room for max_locks_per_transaction * max_connections entries,so as soon as it exceeds,you will get this error message. For fixing this issue,please set max_locks_per_transaction to (tables in database)/max_connections. Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] installation problem with postgres password
On Thu, Jul 12, 2012 at 8:35 AM, KOTa wrote: > that is what i though, but i tried many different combinations for > password, and at least 1 of them should satisfy policy requirements. > also my windows user password, which is accepted by OS and thus > satisfies the requirements resulted in same message > > i did try to see what policy is active, but i am using win 7 Home > Premium and i could find a way for this version to see/change > policies. > the only thing i found is by running "net accounts" from command > prompt. which shows the following: > > http://gyazo.com/be1089b5f0a1ebe6b8037ad9322eac31.png?1342061599 > > and those are very simple password policy which all combinations i used > satisfy > Could you please try to reset the "postgres" user password in window7 before reinstalling the PostgreSQL. Below link gives more information of reseting password in windows7 http://pcsupport.about.com/od/windows7/ht/change-password-windows-7.htm Thanks & Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/
Re: [GENERAL] Is there a way to recover deleted records if database has not been vacuumed?
On Mon, Jul 2, 2012 at 3:21 PM, Frank Church wrote: > > I am using Django to develop an app and I think I must have done a syncdb > (which deletes all records) without realizing it. > > I have not vacuumed that database and I have also made a copy of the data > directory. > > Is there some way to recover the deleted records? > > You can perform Point in time recovery to recover the deleted records,if you have enabled database in WAL archiving mode. Below URL provides more information of the Point-in-time recovery on PostgreSQL http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/ Thanks & Regards, Raghu Ram
Re: [GENERAL] PostgreSQL Slony-I Replication
On Sun, Jul 1, 2012 at 10:18 PM, Iqbal Aroussi wrote: > Hi dear friends, > > This my first post to PostgreSQL mailing list. > > I'm trying to setup a master-slave replication with PostgreSQL 9.1 / > Slony-I, this is first time I'm doing it and I'm kind of lost :( > Is there any tutorial explaining the steps how to do it with *FreeBSD* ? > > I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 / > slony1v2-2.1.1 > > Thanks a lot in advance > > Below URL's provides more information about slony Replication setup: http://raghavt.blogspot.in/2012/07/simple-slony-i-replication-setup.html Thanks & Regards, Raghu Ram
Re: [GENERAL] Regarding parallel running of 9.1 and 9.2beta2
> > Thanks Raghu. > > BTW,could you solve my other(makefile) issue please? > > Atri > > > Sorry about Makefile issue, someone would have better solution on it.. -Raghu Ram
Re: [GENERAL] Regarding parallel running of 9.1 and 9.2beta2
> > > Hi John, > > What values should I be setting in PGDATA and PGPORT? > > Below URL provides more information on this: http://www.postgresql.org/docs/9.1/static/libpq-envars.html -- Thanks & Regards, Raghu Ram
Re: [GENERAL] Regarding parallel running of 9.1 and 9.2beta2
Hi Raghu, > > Thanks for your reply. > > I ran make install in my 9.1 directory again,and this time,the server > started but is giving errors in running the code which was running > earlier.Could you tell me a way in which I could remove both 9.1 and > 9.2beta2 and build both from scratch and install them independently > this time? > cd ./configure make distclean Remove "pgsql" directory from /usr/local and then you can build both from scratch and install them independently -- Thanks & Regards, Raghu Ram
Re: [GENERAL] Regarding parallel running of 9.1 and 9.2beta2
On Fri, Jun 29, 2012 at 1:47 PM, Atri Sharma wrote: > Hi, > > I am running PostgreSQL 9.1 currently and I want to run 9.2beta2 > simultaneously with it.Is it possible? > > Yes, You can have multiple PostgreSQL versions on same host. I downloaded the 9.2beta2 sources,untared them and ran > ./configure,make and make install without uninstalling 9.1. > ./configure --prefix= make make install /bin/initdb -D modify "Port" number in postgresql.conf file located under /bin/pg_ctl -D start > > Now,when I am running pg_ctl start,I am getting the following error: > > pg_ctl start > server starting > gitc@ubuntu:~/Downloads$ FATAL: database files are incompatible with > server > DETAIL: The data directory was initialized by PostgreSQL version 9.1, > which is not compatible with this version 9.2beta2. > > Please help me.What should I be doing to recover from this error and > get back to 9.1? > > Suspecting for this error message is, binaries upgraded upto 9.2beta2 & PostgreSQL instance running with PostgreSQL 9.1 binaries and again you are trying to start instance with pg_ctl of 9.2beta2 binary. -- Thanks & Regards, Raghu Ram
Re: [GENERAL] function lookup using a "real" function call
On Thu, Jun 28, 2012 at 3:30 PM, Marc Mamin wrote:
>
> Hello,
>
> Is it possible to find out the (list of) function oids from a function
> call?
>
> e.g. select MYFUNC('foo', 1234)
>
> => someting like
>
> select * from get_function_oids($$MYFUNC('foo', 1234)$$)
>
>
>
Below approach provides information of the OID of the function::
postgres=# create function myfunc(int) returns int as 'select $1' language
sql;
CREATE FUNCTION
postgres=# SELECT 'myfunc(int)'::regprocedure::oid;
oid
---
24612
(1 row)
postgres=# select 24612::regprocedure;
regprocedure
-
myfunc(integer)
--
Thanks & Regards,
Raghu Ram
Re: [GENERAL] Postgres no longer starts
On Wed, May 30, 2012 at 8:55 PM, Bart Lateur wrote: > CentOS 5.x (now 5.8), Postgres 8.4.something. Postgres had been up and > running for over a year now. > > ** ** > > After an update on the system, and adding mod_ssl in Apache (is this > related? No idea.), Postgres no longer starts up. It just fails silently. > “pgstartup.log” contains only one single line: > > ** ** > > runuser: cannot set groups: Operation not permitted > > ** ** > > ** ** > > I have no idea where to begin to debug this; Google only produces a > handful of results about this error message in relation to Postgres. None fix > my problem. > > ** ** > > Whatever I do, everything just fails quietly. As far as I can see, the > configuration files haven’t changed. > > ** ** > > Help? Where do I even begin to debug this? Could a complete reinstall of > Postgres and restoration of the databases help? > > ** ** > > -- > > Bart. > > ** ** > > ** ** > Could you please, try to start the cluster using "pg_ctl" utility: Syntax: /bin/pg_ctl -D start Ex:- /opt/PostgreSQL/8.4/bin/pg_ctl -D /opt/PostgreSQL/8.4/data start and then share the log file information -- Thanks & Regards, Raghu Ram Skypeid: raghu.ramedb EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] postgresql standby using pg_archivecleanup don't work
On Fri, Apr 27, 2012 at 11:44 AM, leo xu wrote: > i have one parimary ,two standby. one standby using stream replication > connect to primary.another standby using basebackup,then recovery database > --cp archivelog from primary using crontab.i want to delete old archivelog > from standby.but configure pg_archivecleanup don't work,meantime no error > in > postgresql log.postgresql version is 9.1.2. > the following is my recovery.conf : > This is my recovery.conf: > > standby_mode = 'on' > restore_command = 'cp /attend_pg_backup/u02/pg9.1/archivelog/%f %p' > archive_cleanup_command ='pg_archivecleanup > /attend_pg_backup/u02/pg9.1/archivelog %r' > > > > Replace archive_cleanup_command in recovery.conf file as follows: archive_cleanup_command ='pg_archivecleanup /attend_pg_backup/u02/pg9.1/archivelog %r' [to] archive_cleanup_command = '/opt/PostgreSQL/9.1/bin/pg_archivecleanup -d /attend_pg_backup/u02/pg9.1/archivelog %r 2>>/tmp/cleanup.log' and then restart the Standby PostgreSQL cluster. -- Thanks & Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
On Sat, Apr 14, 2012 at 6:35 PM, Clodoaldo Neto < clodoaldo.pinto.n...@gmail.com> wrote: > Em 14 de abril de 2012 09:39, raghu ram escreveu: > >> >> >> On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto < >> clodoaldo.pinto.n...@gmail.com> wrote: >> >>> Em 12 de abril de 2012 14:12, Phoenix Kiula >>> escreveu: >>> >>>> I had pgbouncer working somehow, but we have switched servers recently >>>> and now I cannot for the life of me figure out again how to set it up. >>>> >>>> Online guides say things like "create a user ID". Well, where? Inside >>>> PG the database? Or in my CentOS system? >>>> >>>> Here's my "/etc/pgbouncer.ini": >>>> >>>>[databases] >>>>* = port = 5432 >>>> >>>>[pgbouncer] >>>>listen_port = 6543 >>>>listen_addr = 127.0.0.1 >>>>auth_type = trust >>>>auth_file = /var/lib/pgsql/pgbouncer.txt >>>>logfile = pgbouncer.log >>>>pidfile = pgbouncer.pid >>>>admin_users = postgres,MYSITE_pgbouncer >>>>pool_mode = transaction >>>>server_reset_query = DISCARD ALL; >>>>server_check_query = select 1 >>>>server_check_delay = 10 >>>>max_client_conn = 1000 >>>>default_pool_size = 20 >>>>log_connections = 0 >>>>log_disconnections = 0 >>>>log_pooler_errors = 1 >>>> >>>> >>>> And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" -- >>>> >>>>"MYSITE_pgbouncer" "" >>>> >>>> Is there something else I need to do? What steps am I missing? When I >>>> start pgbouncer at the command line, I see this error: >>>> >>>> WARNING: password file "/root/.pgpass" has group or world access; >>>> permissions should be u=rw (0600) or less >>>> psql: ERROR: No such user: MYSITE_pgbouncer >>>> >>>> >>> I had this error in the pgbouncer log file after updating to the last >>> version, 1.5, in Centos 6. For me the fix was to set the ownership of the >>> auth_file to pgbouncer. In the previous version, 1.4, it was working with >>> postgres as the owner of that file. >>> >>> Regards, Clodoaldo >>> >> >> Could you please share stats_users information in pgbouncer.ini file ? >> >> > That is the default: > > stats_users = stats, root > Could you please modify and below parameters in pgbouncer.ini file: admin_users = postgres,MYSITE_pgbouncer stats_users = postgres,MYSITE_pgbouncer, stats, root and then Restart the pgbouncer. -- Thanks & Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto < clodoaldo.pinto.n...@gmail.com> wrote: > Em 12 de abril de 2012 14:12, Phoenix Kiula escreveu: > >> I had pgbouncer working somehow, but we have switched servers recently >> and now I cannot for the life of me figure out again how to set it up. >> >> Online guides say things like "create a user ID". Well, where? Inside >> PG the database? Or in my CentOS system? >> >> Here's my "/etc/pgbouncer.ini": >> >>[databases] >>* = port = 5432 >> >>[pgbouncer] >>listen_port = 6543 >>listen_addr = 127.0.0.1 >>auth_type = trust >>auth_file = /var/lib/pgsql/pgbouncer.txt >>logfile = pgbouncer.log >>pidfile = pgbouncer.pid >>admin_users = postgres,MYSITE_pgbouncer >>pool_mode = transaction >>server_reset_query = DISCARD ALL; >>server_check_query = select 1 >>server_check_delay = 10 >>max_client_conn = 1000 >>default_pool_size = 20 >>log_connections = 0 >>log_disconnections = 0 >>log_pooler_errors = 1 >> >> >> And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" -- >> >>"MYSITE_pgbouncer" "" >> >> Is there something else I need to do? What steps am I missing? When I >> start pgbouncer at the command line, I see this error: >> >> WARNING: password file "/root/.pgpass" has group or world access; >> permissions should be u=rw (0600) or less >> psql: ERROR: No such user: MYSITE_pgbouncer >> >> > I had this error in the pgbouncer log file after updating to the last > version, 1.5, in Centos 6. For me the fix was to set the ownership of the > auth_file to pgbouncer. In the previous version, 1.4, it was working with > postgres as the owner of that file. > > Regards, Clodoaldo > Could you please share stats_users information in pgbouncer.ini file ? -- Thanks & Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] how to list all the schema and the privileges which is granted to users.THanks.
On Sat, Apr 14, 2012 at 6:47 AM, leaf_yxj wrote:
> how to list all the schema and the privileges which is granted to
> users.THanks.
>
> I know how to list all the objects privileges :
> \dp
>
> But I don't know how to list the schema's privileges??
>
Schema Privileges:
postgres=# \dn+
List of schemas
Name| Owner | Access privileges |
Description
+--+-+--
information_schema | postgres | {postgres=UC/postgres,=U/postgres} |
pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} |
system catalog schema
pg_toast | postgres | |
reserved schema for TOAST tables
pg_toast_temp_1| postgres | |
public | postgres | {postgres=UC/postgres,=UC/postgres} |
standard public schema
(5 rows)
--
Thanks & Regards,
Raghu Ram
EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] recommended schema diff tools?
On Thu, Apr 12, 2012 at 9:35 PM, Thomas Kellerer wrote: > Welty, Richard wrote on 12.04.2012 16:57: > > can anyone recommend an open source tool for diffing schemas? >> >> (it should go without saying that i'm looking for ddl to update >> production and QA DBs from development DBs, but i'll say it, just in >> case.) >> > Take the dumps of two schemas in plain format like this: pg_dump.exe -Fp -U edb -s -n schema1 edb > db.out pg_dump.exe -Fp -U edb -s -n schema2 edb > db1.out Now you can use a tool like *Kompare [ http://www.kde.org/applications/development/kompare/]* to get a diffbetween two schema files and see where there is a change in the schema. -- Thanks & Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] Re: Hot Standby - ERROR: canceling statement due to conflict with recovery
On Fri, Apr 13, 2012 at 2:09 AM, Francois wrote: > One solution, for backups anyway, is to pause the replication on the slave > machines with the command: pg_xlog_replay_pause() and > pg_xlog_replay_resume(). We still don't have a solution for long running > queries, mayber have a mechanism to retry them when they fail with that > specific error. > For resolving this issue,Set below parameters values equal to total execution time of long running statements in Postgresql.conf of Standby server. max_standby_archive_delay max_standby_streaming_delay and then RELOAD the PostgreSQL cluster. -- Thanks & Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] PostgreSQL pgstat wait timeout question
On Tue, Apr 10, 2012 at 12:39 PM, tuanhoanganh wrote: > I am running windows 2003 R2 (64 bit) - PostgreSQL 9.0.6 - 64bit. > Today PostgreSQL has alot pgstat wait timeout in log. > How to fix the PostgreSQL WARNING?? > > Tuan Hoang ANh > > This warning message shows in database server log file, if a process gets interrupted at unfortunate moment, and doesn't get to run for a very long time. --Raghu
Re: [GENERAL] Installing different PostgreSQL versions in parallel
On Tue, Dec 6, 2011 at 12:13 AM, Rafael Ostertag wrote: > Hi list > > I'm in the course of creating PostgreSQL packages for Solaris. One of the > requirement is to install different PostgreSQL versions in parallel, the > other > is to have all the libraries stuffed in one place. > > Now, if I install, let's say, pgsql 8.3 and 8.4 in parallel and all > libraries > go to /opt/csw/lib, I end up with: > > $ ls -l /opt/csw/lib/libpq* > lrwxrwxrwx 1 root root libpq.so -> libpq.so.5.2 > lrwxrwxrwx 1 root root libpq.so.5 -> libpq.so.5.2 > -rwxr-xr-x 1 root bin libpq.so.5.1 > -rwxr-xr-x 1 root bin libpq.so.5.2 > > Which in turn leads to 8.3 binaries loading libpq.so.5.2, effectively > mixing > 8.3 binaries with 8.4 libraries. > > Finally, the question: How bad is it having a pgsql binary version X.Y > using a > library X.Z? > > To overcome this issues,use "--prefix=PREFIX " option while running "configure",so that PostgreSQL 8.3 version related directories [i.e bin,include,lib,share] will store in preferred PREFIX location. Ex: ./configure --prefix=/usr/local/pgsql83 ./configure --prefix=/usr/local/pgsql84 Once completion of PostgreSQL installation,you will see "bin,include,lib,share" directories are created under the /usr/local/pgsql83 location. --Raghu Ram
Re: [GENERAL] archive_timeout runtime change
2011/10/20 Birta Levente > Hi > > I use postgrsql 9.1.1 and I want to change archive_timeout on a running > server. My default archive_timeout is 1800s, and nightly I want to change to > 3600s or more, but I get the following error message: > > set archive_timeout='3600'; > ERROR: parameter "archive_timeout" cannot be changed now > > and > > update pg_settings set setting='900' where name='archive_timeout'; > ERROR: parameter "archive_timeout" cannot be changed now > > I know is possible to change this parameter with pg_ctl reload, but I think > if is possible without changing the conf file. > > postgres=# select name,setting,context from pg_settings where name like '%archive%'; name| setting| context ---+--+ archive_timeout | 0| sighup You have to edit the configuration file and then reload the Database server to affect these changes. You can't change the "archive_timeout" value without editing the PostgreSQL.conf file. Below link describes,changing the postgresql.conf from a script: http://blog.endpoint.com/2011/08/changing-postgresqlconf-from-script.html --Raghu
Re: [GENERAL] Temporally disabled foreign key constraint check?
On Fri, Oct 21, 2011 at 9:19 PM, Emi Lu wrote: > Thank you first. > > I believe that upate pg_class can only be done by superuser, right? > Yes,it's requires superuser privileges. > > Besides, if I need the whole schema's foreign keys to be disabled and then > enabled later. > > Is there a simple command could do it? Similar to mysql's "set > FOREIGN_KEY_CHECKS = false/true"? > > No. The main goal for PG is to secure data integrity, and you can't switch off this feature. Disable the constraints: alter table disable trigger all; You can use this command inside your transaction too, but don't forget to reactivate the triggers because this change will become global after the transaction ends! --Raghu > On 10/21/2011 11:12 AM, raghu ram wrote: > >> >> >> On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu > <mailto:emilu@encs.concordia.**ca >> wrote: >> >>Good morning, >> >> >>Is there a way to temporally disabled foreign key constraints >>something like: >> >>SET FOREIGN_KEY_CHECKS=0 >> >>When population is done, will set FOREIGN_KEY_CHECKS=1 >> >> >> You can disable *triggers* on a table (which will disable all the FK >> constraints, but not things like 'not nul' or 'unique'). >> >> For Disable: >> >> update pg_class set reltriggers=0 where relname = 'TEST'; >> >> For Enable: >> >> update pg_class set reltriggers = count(*) from pg_trigger where >> pg_class.oid=tgrelid and relname='TEST'; >> >> --Raghu >> > > > -- > Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 > em...@encs.concordia.ca+1 514 848-2424 x5884 >
Re: [GENERAL] Temporally disabled foreign key constraint check?
On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu wrote: > Good morning, > > > Is there a way to temporally disabled foreign key constraints something > like: > > SET FOREIGN_KEY_CHECKS=0 > > When population is done, will set FOREIGN_KEY_CHECKS=1 > > You can disable *triggers* on a table (which will disable all the FK constraints, but not things like 'not nul' or 'unique'). For Disable: update pg_class set reltriggers=0 where relname = 'TEST'; For Enable: update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='TEST'; --Raghu
Re: [GENERAL] get old versions (8.3.8 or 8.4.1)
2011/8/16 Vítor Carvalho > Hi, > > ** ** > > I need install the version 8.3.8 or 8.4.1 of the PostgreSQL in one SLES 10, > where can I get one of this versions? > > > Below are links to download the source module of PostgreSQL 8.3.8 & PoatgreSQL 8.4.1 : * * *ftp://ftp-archives.postgresql.org/pub/source/v8.3.8/postgresql-8.3.8.tar.gz * *ftp://ftp-archives.postgresql.org/pub/source/v8.4.1/postgresql-8.4.1.tar.gz * *--Raghu Ram*
Re: [GENERAL] Check server mode (standby / master)
On Tue, Aug 16, 2011 at 1:16 PM, Alexander Perepelica < perepelica.a...@gmail.com> wrote: > Hello! > > How can I check which mode has a sever: master or slave (standby) after I > setup replication? > > You can check the mode of the server using "pg_controldata". [pgsql@test~]$ pg_controldata /usr/local/pgsql/data84/ Database cluster state: in archive recovery --> This is Standby Database Database cluster state: in production --> This is Production Database [Master] --Raghu Ram
Re: [GENERAL] Postgredac Dump
On Thu, May 12, 2011 at 10:02 PM, Bob Pawley wrote: > Hi > > Can anyone point me to an example of how to use the Postgresdac Dump > component?? > > > Below URL will give more detail information about Postgresdac dump:: http://www.microolap.com/products/connectivity/postgresdac/help/TPSQLDump/Methods/DumpToFile.htm --Raghu Ram
Re: [GENERAL] [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 6:01 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > > On Tue, May 3, 2011 at 5:37 PM, Simon Riggs wrote: > >> On Tue, May 3, 2011 at 11:54 AM, raghu ram >> wrote: >> >> > It may be a silly question, still out of curiosity I want to know, is >> there >> > any possible way to flush the Postgres Shared Memory without restarting >> the >> > cluster. >> > In Oracle, we can flush the SGA, can we get the same feature here.. >> > Thanks in Advance. >> >> >> The CHECKPOINT command will do this for you. >> > > > According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in the database,it will flush the modified data files presented in the Shared Buffers retuned to the Disk. http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html Is this clears the entire shared memory cache and same time,if i execute fresh SQL statement, Data will be retuned from disk?? --Raghu Ram
[GENERAL] Can we Flush the Postgres Shared Memory ?
Respected Committers, It may be a silly question, still out of curiosity I want to know, is there any possible way to flush the Postgres Shared Memory without restarting the cluster. In Oracle, we can flush the SGA, can we get the same feature here.. Thanks in Advance. Regards Raghu Ram EnterpriseDB Corporation
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
On Thu, Apr 21, 2011 at 9:19 PM, David Fetter wrote:
> On Wed, Apr 20, 2011 at 11:51:25AM -0400, Emi Lu wrote:
> > Hello,
> >
> > ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to
> > get row_number
> >
> > select row_number(), col1, col2...
> > FROM tableName
> >
> > Thanks a lot!
> > 丁叶
>
> Your best bet is to upgrade to a modern version of PostgreSQL. While
> you will of course need to do tests with your applications, 9.0 has no
> significant backward-incompatibility with 8.3.
>
Hmm, PostgreSQL 8.3 does not support the ROWNUM feature, however, a possible
work around can be achieved by using the LIMIT and OFFSET options.
psql=# SELECT empno FROM emp LIMIT 10
The above query will display the first 10 records.
You can also use the (auto incrementing) SERIAL data type as a ROWNUM column
to simulate the ROWNUM feature.
Something like this...
psql=# create table rownumtest(rownum SERIAL, val1 varchar, val2 int4);
psql=# insert into rownumtest(val1,val2) values('abc', '1');
psql=# insert into rownumtest(val1,val2) values('def', '2');
psql=# insert into rownumtest(val1,val2) values('ghi', '3');
psql=# insert into rownumtest(val1,val2) values('jkl', '4');
psql=# select * from rownumtest;
rownum | val1 | val2
+--+--
1 | abc | 1
2 | def | 2
3 | ghi | 3
4 | jkl | 4
Hope this helps
--Raghu Ram
Re: [GENERAL] Needs Suggestion
On Wed, Apr 20, 2011 at 10:41 PM, SUBHAM ROY wrote: > By doing \timing in psql, we enable the timing and then when we type the > query we are able to see its execution time. > Similarly, is there any way to view the number I/Os and memory usage by a > particular query. > > PGsnap module will describes tables,Indexes,Sequences I/O statistics and URL as follows http://pgsnap.projects.postgresql.org/fluxbb_snap_20100513/bgwriter.html pg_buffercache module will shows memory usage on particular tables. --Raghu Ram > And also the timing result that gets displayed, in which log file does it > get recorded? > > -- > Thank You, > Subham Roy, > CSE IIT Bombay. > >
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
On Wed, Apr 20, 2011 at 9:21 PM, Emi Lu wrote: > Hello, > > ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get > row_number > > select row_number(), col1, col2... > FROM tableName > > Below link will demonstrates ROW_NUMBER features in pre Postgresql-8.4:: http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-in-PostgreSQL-Pre-8.4.html --Raghu Ram > Thanks a lot! > 丁叶 > > -- > 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 uninstall PostgreSQL 8.4 on both Windows XP and Windows 7
On Wed, Apr 20, 2011 at 8:39 PM, Mlondolozi Ncapayi wrote: > Thank Sir, I have managed to uninstall it. > Now I installed PostgreSQL 8.4.4 and PostGIS 1.4.2. > > Can you please send me command prompts to load shapefiles using Windows 7. > > I want to load cities.shp into template_postgis14 [dbname] > > This command does not work > > shp2pgsql cities.shp public.cities cities.sql followed by psql -d > template_postgis14 -f cities.sql -U postgres > > > 1)Convert shp to sql user> shp2pgsql -s 4326 test_AK.shp mp_census_block > mp_census_block.sql 2)Insert into the database user> psql -U test -d DatabaseName -f mp_census_block.sql --Raghu Ram > > On Wed, Apr 20, 2011 at 4:31 PM, raghu ram wrote: > >> >> >> On Wed, Apr 20, 2011 at 12:32 PM, Mlondolozi Ncapayi > > wrote: >> >>> Hi there >>> >>> I installed PostgreSql 8.4 and now I want to delete/ uninstall it >>> completely to start a new fresh installation. >>> Can you please give me clear instructions on how to do that or maybe a >>> script that I can run. >>> I am doing assignment using postgres your help would be highly >>> appreciated. >>> >> >> >> Please go to the postgresql server installation directory and here you >> will find one uninstall-postgresql file. >> >> -double click on Uninstall-postgresql file to run the un-installer- it >> will un-install postgresql. >> >> >> II)Also you can un-install it manually.Below are the steps to un-install >> postgresql 8.4 manually- >> >> >> >> i) Remove the postgresql server installation directory. (rd /s /q >> "C:\Program Files\PostgreSQL\8.4") Assuming default location. >> >> ii) Delete the user 'postgres' (net user postgres /delete) >> >> iii) Remove the Registry entries. (HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL >> \Installations\postgresql-8.4) and (HKEY_LOCAL_MACHINE\SOFTWARE\ >> PostgreSQL\Services\postgresql-8.4) >> >> iv) Remove the postgresql-8.4 service. (sc delete postgresql-8.4) >> >> >> >> This will completely remove your PostgreSQL 8.4 installation including >> user and installation directories. >> >> --Raghu Ram >> >> >> >>> >>> Mlo >>> >>> >>> >> >> >
Re: [GENERAL] How to uninstall PostgreSQL 8.4 on both Windows XP and Windows 7
On Wed, Apr 20, 2011 at 12:32 PM, Mlondolozi Ncapayi wrote: > Hi there > > I installed PostgreSql 8.4 and now I want to delete/ uninstall it > completely to start a new fresh installation. > Can you please give me clear instructions on how to do that or maybe a > script that I can run. > I am doing assignment using postgres your help would be highly appreciated. > Please go to the postgresql server installation directory and here you will find one uninstall-postgresql file. -double click on Uninstall-postgresql file to run the un-installer- it will un-install postgresql. II)Also you can un-install it manually.Below are the steps to un-install postgresql 8.4 manually- i) Remove the postgresql server installation directory. (rd /s /q "C:\Program Files\PostgreSQL\8.4") Assuming default location. ii) Delete the user 'postgres' (net user postgres /delete) iii) Remove the Registry entries. (HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL \Installations\postgresql-8.4) and (HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL \Services\postgresql-8.4) iv) Remove the postgresql-8.4 service. (sc delete postgresql-8.4) This will completely remove your PostgreSQL 8.4 installation including user and installation directories. --Raghu Ram > > Mlo > > >
[GENERAL] Streaming Replication limitations
Hi, Is there any limitations to configure streaming replication between different operating systems i.e solaris 64 bit to RHEL 64 bit. --Raghu Ram
[GENERAL] Re: [ADMIN] What does error "psql: Kerberos 5 authentication not supported" means?
On Thu, Mar 24, 2011 at 6:49 PM, Willy-Bas Loos wrote: > BTW, is there a way to find out what particular configure options were used > for any given (binary) installation? > > The *pg_config* utility prints configuration parameters of the currently installed version of PostgreSQL *--configure* Print the options that were given to the *configure* script when PostgreSQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built. --Raghu Ram > WBL > > > On Thu, Mar 24, 2011 at 2:11 PM, Willy-Bas Loos wrote: > >> Hi, >> >> I'm not quite sure but it probably means that postgresql was not built >> with kerberos 5 support. >> That would make sense if you are using a binary install (did not compile >> from source), because of the notice at >> http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#KERBEROS-AUTH >> "Note: Native Kerberos authentication has been deprecated and should be >> used only for backward compatibility. New and upgraded installations are >> encouraged to use the industry-standard GSSAPI authentication method (see >> Section 19.3.3) instead." >> >> hth, >> >> WBL >> >> >> On Wed, Mar 23, 2011 at 5:44 PM, wrote: >> >>> Hi Guys, >>> >>> I tried to setup kerberos authentication with postgresql 8.4.7 on Ubuntu >>> 10.10. >>> >>> When I use: >>> $ psql –h ubuntu.server –U gavin dbname >>> >>> I get following error: >>> psql: Kerberos 5 authentication not supported. >>> >>> And from the server side of postgresql, I could see following log: >>> >>> 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 >>> 2011-03-23 09:14:17 PDT DEBUG: 0: exit(0) >>> 2011-03-23 09:14:17 PDT LOCATION: proc_exit, ipc.c:135 >>> 2011-03-23 09:14:17 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to >>> make >>> 2011-03-23 09:14:17 PDT LOCATION: shmem_exit, ipc.c:211 >>> 2011-03-23 09:14:17 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make >>> 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 >>> 2011-03-23 09:14:17 PDT DEBUG: 0: reaping dead processes >>> 2011-03-23 09:14:17 PDT LOCATION: reaper, postmaster.c:2243 >>> 2011-03-23 09:14:17 PDT DEBUG: 0: server process (PID 4592) exited >>> with exit code 0 >>> 2011-03-23 09:14:17 PDT LOCATION: LogChildExit, postmaster.c:2725 >>> 2011-03-23 09:14:24 PDT LOG: 0: Kerberos recvauth returned error 103 >>> 2011-03-23 09:14:24 PDT LOCATION: pg_krb5_recvauth, auth.c:721 >>> postgres: Software caused connection abort from krb5_recvauth >>> 2011-03-23 09:14:24 PDT FATAL: 28000: Kerberos 5 authentication failed >>> for user "gavin" >>> 2011-03-23 09:14:24 PDT LOCATION: auth_failed, auth.c:273 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(1): 0 callbacks to make >>> 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(1): 1 callbacks to make >>> 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: exit(1) >>> 2011-03-23 09:14:24 PDT LOCATION: proc_exit, ipc.c:135 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to >>> make >>> 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make >>> 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 >>> 2011-03-23 09:14:24 PDT DEBUG: 0: reaping dead processes >>> >>> What does this means and how could I get more verbose logs? >>> >>> Besides, is there any more howto/doc on integrate kerberos and postgresql >>> besides the official documentation at >>> http://www.postgresql.org/docs/9.0/interactive/client-authentication.html >>> ? >>> >>> >>> Thanks very much in advance! >>> >>> Regards, >>> Yandong >>> >>> >>> >>> -- >>> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-admin >>> >> >> >> >> -- >> "Patriotism is the conviction that your country is superior to all others >> because you were born in it." -- George Bernard Shaw >> > > > > -- > "Patriotism is the conviction that your country is superior to all others > because you were born in it." -- George Bernard Shaw >
[GENERAL] Pglesslog issue
Hi, I was installed the Postgresql 8.3 and trying the use the "pg_lesslog_1.4.1_pg83" to reduce the size of WAL file when the WAL file is archived. 1. Download the "pg_lesslog_1.4.1_pg83.tar.gz" file from pgfoundry. 2. unpacked the pglesslog source. 3. trying to run the "make"...facing below issue:: edbs-MacBook-4:*pg_lesslog_1.4.1_pg83 root# make* make -f Makefile.pg_compresslog all Makefile.pg_compresslog:21: ../../src/Makefile.global: No such file or directory Makefile.pg_compresslog:22: /contrib/contrib-global.mk: No such file or directory make[1]: *** No rule to make target `/contrib/contrib-global.mk'. Stop. make: *** [all] Error 2 could you please guide me the installation steps... Regards Raghu
Re: [GENERAL] Prepared statement issue in Pgpool-II
On Tue, Jun 29, 2010 at 3:23 PM, Vibhor Kumar wrote: > On 28/06/10 11:14 PM, raghu ram wrote: > >> >> Hi, >> >> >> >> We are using pgool-II version 2.3.2.2 (tomiteboshi)and postgres 8.3.9 and >> facing the below error while running the select queries in the application >> side:: >> >> >> ERROR: prepared statement >> "mdb2_statement_pgsql_71417ca02c1aa9cec1dd2570c46e992e" does not exist >> >> > Try with reset_query_list= 'ABORT; DISCARD ALL' > > Thanks Vibhor... If you set the reset_query_list = 'ABORT; DISCARD ALL' (or) reset_query_list = 'ABORT; DISCARD TEMPORARY' also getting the same error again for both version of the Pgpool-II. Thanks&Regards Raghu > -- > Thanks& Regards, > Vibhor Kumar. > EnterpriseDB Corporation > The Enterprise Postgres Company > > Office: 732-331-1300 Ext-2022 > > Website: www.enterprisedb.com > EnterpriseDB Blog: http://blogs.enterprisedb.com > Follow us on Twitter: http://www.twitter.com/enterprisedb > >
[GENERAL] Prepared statement issue in Pgpool-II
Hi, We are using pgool-II version 2.3.2.2 (tomiteboshi)and postgres 8.3.9 and facing the below error while running the select queries in the application side:: ERROR: prepared statement "mdb2_statement_pgsql_71417ca02c1aa9cec1dd2570c46e992e" does not exist STATEMENT: DEALLOCATE PREPARE mdb2_statement_pgsql_71417ca02c1aa9cec1dd2570c46e992e WARNING: archive_mode enabled, yet archive_command is not set In the pgpool.conf file set the "reset_query_list" ='ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' We also tested on pgpool-II version 2.3.3, result will be same. Please help us to resolve the issue. Regards Raghu
[GENERAL] Invitation to connect on LinkedIn
LinkedIn I'd like to add you to my professional network on LinkedIn. - raghu Accept raghu ram's invite: https://www.linkedin.com/e/isd/734105299/D0TiGEdR/ -- (c) 2009, LinkedIn Corporation
[GENERAL] Re: [GENERAL] Error ::- “ 'more' is not recognized as an internal or external command, operable program or bat ch file. “
It worked, Thaks!!! On Wed, Jun 24, 2009 at 4:15 AM, Vibhor Kumar wrote: > "more" exists in C:\Windows\System32. So, please set the path in PATH > Environment Variable. and then try. > My Computer (Right Click) -> Properties -> then go to Advanced tab -> > Environment Variable -> set the path. > > > Thanks & Regards, > Vibhor Kumar > www.enterprisedb.com > > > raghu ram wrote: > >> Hi All, >> >> Can anyone tel me what does this error mean. “ 'more' is not recognized as >> an internal or external command, operable program or batch file. “ >> >> >> C:\Program Files\PostgresPlus\8.4\bin>psql -d postgres -p 5456 -U postgres >> psql (8.4rc1) >> WARNING: Console code page (437) differs from Windows code page (1252) >> 8-bit characters might not work correctly. See psql reference >> page "Notes for Windows users" for details. >> Type "help" for help. >> >> >> postgres=# \l >> >> 'more' is not recognized as an internal or external command, >> operable program or batch file. >> >> >
[GENERAL] Error ::- “ 'more' is not recognized as an interna l or external command, operable program or batch file. “
Hi All, Can anyone tel me what does this error mean. “ 'more' is not recognized as an internal or external command, operable program or batch file. “ C:\Program Files\PostgresPlus\8.4\bin>psql -d postgres -p 5456 -U postgres psql (8.4rc1) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. postgres=# \l 'more' is not recognized as an internal or external command, operable program or batch file.
