Re: [GENERAL] regarding table migration from sql to postgres with runmtk.sh
On Wed, Mar 9, 2016 at 7:26 AM, Durgamahesh Manne wrote: > Hi sir > i got following erro when i ran runmtk.sh > > initially i placed the jtds driver related to sql in > /opt/postgresplus/edbmtk/lib > > ./runMTK.sh -sourcedbtype sqlserver -targetdbtype postgresql > feeds_morningstar_bond_sector > Running EnterpriseDB Migration Toolkit (Build 48.0.2) ... > Source database connectivity info... > conn =jdbc:jtds:sqlserver://fxserver.trustfort.com:49888/DataFeedHandler > user =trustfort > password=** > Target database connectivity info... > conn =jdbc:postgresql://192.168.24.128:5432/dbo_sql > user =postgres > password=** > Connecting with source SQL Server database server... > Connected to Microsoft SQL Server, version '10.50.1600' > Connecting with target Postgres database server... > Exception in thread "main" java.lang.NoClassDefFoundError: > org/postgresql/Driver > at > com.edb.dbhandler.postgresql.PGConnection.(PGConnection.java:32) > at > com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:228) > at > com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:5610) > at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3122) > at com.edb.MigrationToolkit.main(MigrationToolkit.java:1521) > Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver > at java.net.URLClassLoader$1.run(URLClassLoader.java:366) > at java.net.URLClassLoader$1.run(URLClassLoader.java:355) > at java.security.AccessController.doPrivileged(Native Method) > at java.net.URLClassLoader.findClass(URLClassLoader.java:354) > at java.lang.ClassLoader.loadClass(ClassLoader.java:425) > at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308) > at java.lang.ClassLoader.loadClass(ClassLoader.java:358) > > Looks like the driver issue. ./runMTK.sh is EnterpriseDB Migration Studio tool. Can you please reach out to EnterpriseDB support or Forum. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Tool for shifting tables from Mysql to Postgresql
Hi Adarsh, There are very good tools out for migration from Mysql to PostgreSQL. EnterpriseDB has the migration studio which will help to migrate Mysql to PostgreSQL. http://www.enterprisedb.com/solutions/mysql-vs-postgresql/how-to-move-from-mysql-to-postgresql Best Regards, Raghavendra EnterpriseDB Corporation On Tue, Mar 1, 2011 at 3:57 PM, Adarsh Sharma wrote: > Dear all, > > I want to convert some tables from Mysql database to Postgresql Database in > Linux Systems ( Ubuntu-10.4, CentOS ). > > Can someone Please tell me tool for it that makes it easier. > > I am able to done it through FW tools in Windows System but i want to > achieve it in Linux ( CentOS ) System. > > I researched a lot & tried below steps : > > 1. mysqldump --compatible=postgresql wiki20100130 > > /hdd4-1/wiki20100130_mysql108feb22.sql > > 2. sed "s/\\\'/\'\'/g" wiki20100130_mysql108feb22.sql > > 3. bin/psql -Upostgres wiki20100130 < > /hdd4-1/wiki20100130_mysql108feb22.sql > > invalid byte sequence for encoding "UTF8": 0xe3ba27 > HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > ERROR: invalid byte sequence for encoding "UTF8": 0xee6c65 > HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > ERROR: invalid byte sequence > > > I think a tool would ease that work. > > Thanks & best Regards, > > Adarsh Sharma > > -- > 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 select a list of sequences?
Hi Kenneth, You can retrieve the sequence information from the pg_catalog "pg_statio_all_sequences" or select * from pg_class where relkind='S' Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler wrote: > How can I select a list of sequences in Postgres 8.4? > > I'm writing functions which select names of tables, tablespaces, sequences, > etc. > > For example, I can select a list of table names using the following > command: > > SELECT tablename FROM pg_tables WHERE schemaname = 'public'; > > However, I can't seem to find a view of sequences. > > I'm sure it's in there, as you can display a list of sequences using > the \ds command, but that doesn't really help me here. > > Any help? > > Thanks, > > Ken > > -- > 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 select a list of sequences?
Thank you. Please do add 'pgsql-general@postgresql.org' while replying. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company On Fri, Mar 4, 2011 at 8:04 PM, Kenneth Buckler wrote: > Perfect! Thanks a bunch! > > Ken > > On Fri, Mar 4, 2011 at 9:31 AM, Raghavendra > wrote: > > Hi Kenneth, > > You can retrieve the sequence information from the pg_catalog > > "pg_statio_all_sequences" > > or > > select * from pg_class where relkind='S' > > Best Regards, > > Raghavendra > > EnterpriseDB Corporation > > The Enterprise Postgres Company > > > > > > On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler < > kenneth.buck...@gmail.com> > > wrote: > >> > >> How can I select a list of sequences in Postgres 8.4? > >> > >> I'm writing functions which select names of tables, tablespaces, > >> sequences, etc. > >> > >> For example, I can select a list of table names using the following > >> command: > >> > >> SELECT tablename FROM pg_tables WHERE schemaname = 'public'; > >> > >> However, I can't seem to find a view of sequences. > >> > >> I'm sure it's in there, as you can display a list of sequences using > >> the \ds command, but that doesn't really help me here. > >> > >> Any help? > >> > >> Thanks, > >> > >> Ken > >> > >> -- > >> 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] list all members in a tablespace
Hi Mike, I tried this, which will get the list of tables belong to 'XYZ' tablespace. select relname from pg_class where reltablespace=(select oid from pg_tablespace where spcname='xyz'); Hope this helps Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company On Thu, Mar 10, 2011 at 6:05 AM, Michael Andrew Babb wrote: > Hi All, > > I'm doing a little housekeeping on my tablespaces and I'm curious if there > is a quick and easy way to list all of the objects in a tablespace. Is there > a command to list all objects in a tablespace? > > Thanks, > > Mike > > -- > 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] Adding PK to Existing Table
Try out this... alter table add primary key(column name); Best Regards, Raghavendra EnterpriseDB Corporation On Fri, Apr 1, 2011 at 9:09 PM, Rich Shepard wrote: > In -9.0.3 I used ALTER TABLE to replace a varchar() column with a bigint > column so it can be assigned as the table's primary key. From the 9.0.3 > manual I tried various flavors of ALTER TABLE ADD CONSTRAINT > but > cannot find the proper syntax to create the PK. Do I need to first make the > column UNIQUE, then add a NOT NULL constraint? > > Rich > > -- > 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 get index columns/dir/ord informations?
Hi, Query to list the tables and its concerned indexes. SELECT indexrelid::regclass as index , relid::regclass as table FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan < 100 AND indisunique IS FALSE; Query will list the contraints. SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.oid=pg_index.indrelid AND ( indisunique = 't' OR indisprimary = 't' ) ); To get the column order number, use this query. SELECT a.attname,a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'vacc' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid; Note: This query for a particular Table 'VACC' Best Regards, Raghavendra EnterpriseDB Corporation On Fri, Apr 1, 2011 at 8:54 PM, Durumdara wrote: > Hi! > > I want to migrate some database to PG. > I want to make intelligens migrator, that makes the list of the SQL-s what > need to do to get same table structure in PG as in the Source DB. > > All things I can get from the views about tables, except the indices. > > These indices are not containing the constraints - these elements I can > analyze. > > I found and SQL that get the index columns: > > > select > t.relname as table_name, > i.relname as index_name, > a.attname as column_name > from > pg_class t, > pg_class i, > pg_index ix, > pg_attribute a > where > t.oid = ix.indrelid > and i.oid = ix.indexrelid > and a.attrelid = t.oid > and a.attnum = ANY(ix.indkey) > and t.relkind = 'r' > and t.relname = 'a' > and ix.indisunique = 'f' > and ix.indisprimary = 'f' > order by > t.relname, > i.relname; > > This can list the columns. But - what a pity - this don't containing that: > - Is this index unique? > - What the direction of the sort by columns > - What is the ordinal number of the column > > So everything what I need to analyze that the needed index is exists or > not. > > > Please help me: how can I get these informations? > I don't want to drop the tables everytime if possible. > > Thanks: > dd >
Re: [GENERAL] How to get index columns/dir/ord informations?
> A pseudo code demonstrate it: > > select * from pg_index_columns where index_name = 'x2' > > Ordinal ColNameIsAsc > 1 a False > 2 b True > > Have PGSQL same information? > > AFAIK, you can pull that information from 'indexdef' column of pg_indexes. select * from pg_indexes where tablename='a'; Best Regards, Raghavendra EnterpriseDB Corporation > Thanks: > dd > > > > > 2011.04.01. 18:01 keltezéssel, Raghavendra írta: > > Hi, > > Query to list the tables and its concerned indexes. > > SELECT indexrelid::regclass as index , relid::regclass as > table FROM pg_stat_user_indexes JOIN pg_index USING > (indexrelid) WHERE idx_scan < 100 AND indisunique IS FALSE; > > Query will list the contraints. > > SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM > pg_index, pg_class WHERE pg_class.oid=pg_index.indrelid AND ( indisunique = > 't' OR indisprimary = 't' ) ); > > To get the column order number, use this query. > > SELECT a.attname,a.attnum FROM pg_class c, pg_attribute a, pg_type t > WHERE c.relname = 'vacc' AND a.attnum > 0 AND a.attrelid = c.oid AND > a.atttypid = t.oid; > > Note: This query for a particular Table 'VACC' > > > Best Regards, > Raghavendra > EnterpriseDB Corporation > > On Fri, Apr 1, 2011 at 8:54 PM, Durumdara wrote: > >> Hi! >> >> I want to migrate some database to PG. >> I want to make intelligens migrator, that makes the list of the SQL-s what >> need to do to get same table structure in PG as in the Source DB. >> >> All things I can get from the views about tables, except the indices. >> >> These indices are not containing the constraints - these elements I can >> analyze. >> >> I found and SQL that get the index columns: >> >> >> select >> t.relname as table_name, >> i.relname as index_name, >> a.attname as column_name >> from >> pg_class t, >> pg_class i, >> pg_index ix, >> pg_attribute a >> where >> t.oid = ix.indrelid >> and i.oid = ix.indexrelid >> and a.attrelid = t.oid >> and a.attnum = ANY(ix.indkey) >> and t.relkind = 'r' >> and t.relname = 'a' >> and ix.indisunique = 'f' >> and ix.indisprimary = 'f' >> order by >> t.relname, >> i.relname; >> >> This can list the columns. But - what a pity - this don't containing >> that: >> - Is this index unique? >> - What the direction of the sort by columns >> - What is the ordinal number of the column >> >> So everything what I need to analyze that the needed index is exists or >> not. >> >> >> Please help me: how can I get these informations? >> I don't want to drop the tables everytime if possible. >> >> Thanks: >> dd >> > > >
Re: [GENERAL] : Getting error while starting the server
> return Database.connect(**conn_params) > File "/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py", line > 164, in connect > conn = _connect(dsn, connection_factory=connection_factory, > async=async) > django.db.utils.OperationalError: could not translate host name > "localhost" to address: Name or service not known > > I guess problem with your server name resolution. Its worth checking as said, using psql -h localhost or ping localhost. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ >
Re: [GENERAL] Unnecessary files that can be deleted/moved in cluster dir?
On Fri, Jan 4, 2013 at 5:01 AM, John Abraham wrote: > I have a little problem, I let my drive get too full. And then while I > was deleting rows to free space, the auto vacuum didn't kick in quite the > way I expected, and I ran out of space entirely. So the DB shut down and > won't start back up. > What message it has written in DB server log file (pg_log) about it not starting again ? You can check the last lines of the recent log file under $PG_DATA/pg_log location, which give very good information about why it has not started. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Error: absolute path not allowed
On Tue, Jan 8, 2013 at 8:39 PM, Wolf Schwurack wrote: > I am getting a repeating error and not sure what why. > > ** ** > > 2013-01-08 06:12:08 MSTERROR: absolute path not allowed > > 2013-01-08 06:12:08 MSTSTATEMENT: SELECT pg_read_file($1, 0, 1048576) > > 2013-01-08 06:42:10 MSTERROR: absolute path not allowed > > 2013-01-08 06:42:10 MSTSTATEMENT: SELECT pg_read_file($1, 0, 1048576) > > 2013-01-08 07:12:01 MSTERROR: absolute path not allowed > > 2013-01-08 07:12:01 MSTSTATEMENT: SELECT pg_read_file($1, 0, 1048576) > > 2013-01-08 07:42:02 MSTERROR: absolute path not allowed > > 2013-01-08 07:42:02 MSTSTATEMENT: SELECT pg_read_file($1, 0, 1048576) > > ** > pg_read_file() search's from $PGDATA location. Can we see how pg_read_file() has been called ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Error: absolute path not allowed
On Thu, Jan 10, 2013 at 1:20 AM, wschwurack wrote: > How do I find what is calling pg_read_file > > As said, check the log's to find DB,Host,User etc. from where its been called. If log_line_prefix not set to appropriate to all those details then change it as shown below which just need RELOAD of server. After changing your logs should show those details. Eg:- log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u client=%h' Also, its just my guess, if pg_read_file() function called from any other function then you can check with below query. select proname from pg_proc where prosrc ilike '%pg_read_file%' and pronamespace=(select oid from pg_namespace where nspname='public'); --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Logging affected rows
On Thu, Jan 17, 2013 at 9:18 AM, classical_89 wrote: > When i run a query , i just only want to the affected rows are logged to > log > file,What can i do ? .Is there a parameter in postgresql.conf to do that ? > .Thanks in advance > > AFAIK, I don't think we can log only affected rows of the query. But you can log the EXPLAIN ANALYZE output of the query which includes affected rows using AUTO_EXPLAIN contrib module. *Eg:-* postgres=# set auto_explain.log_analyze TO on; SET postgres=# load 'auto_explain'; LOAD postgres=# select count(*) from cities ; count --- 2 (1 row) * * *In logs:* 2012-12-06 03:27:57.104 IST [17410]: [78-1] db=postgres,user=postgres LOG: statement: select count(*) from cities ; 2012-12-06 03:27:57.105 IST [17410]: [79-1] db=postgres,user=postgres LOG: duration: 0.024 ms plan: Query Text: select count(*) from cities ; Aggregate (cost=1.02..1.03 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1) Output: count(*) -> Seq Scan on public.cities (cost=0.00..1.02 rows=2 width=0) (actual time=0.009..0.010 *rows=2* loops=1) Output: city, location --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Logging affected rows
On Sat, Jan 19, 2013 at 12:53 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Thu, Jan 17, 2013 at 9:18 AM, classical_89 wrote: > >> When i run a query , i just only want to the affected rows are logged to >> log >> file,What can i do ? .Is there a parameter in postgresql.conf to do that ? >> .Thanks in advance >> >> > AFAIK, I don't think we can log only affected rows of the query. But you > can log the EXPLAIN ANALYZE output of the query which includes affected > rows using AUTO_EXPLAIN contrib module. > > *Eg:-* > > postgres=# set auto_explain.log_analyze TO on; > SET > postgres=# load 'auto_explain'; > LOAD > postgres=# select count(*) from cities ; > count > --- > 2 > (1 row) > * > * > *In logs:* > > 2012-12-06 03:27:57.104 IST [17410]: [78-1] db=postgres,user=postgres LOG: > statement: select count(*) from cities ; > 2012-12-06 03:27:57.105 IST [17410]: [79-1] db=postgres,user=postgres LOG: > duration: 0.024 ms plan: > Query Text: select count(*) from cities ; > Aggregate (cost=1.02..1.03 rows=1 width=0) (actual > time=0.015..0.015 rows=1 loops=1) > Output: count(*) > -> Seq Scan on public.cities (cost=0.00..1.02 rows=2 width=0) > (actual time=0.009..0.010 *rows=2* loops=1) > Output: city, location > > Correction to my previous email, first you need to LOAD module and then enable log_analyze. Copy/Paste mistake :) --Raghav
Re: [GENERAL] Cannot install postgres 9.2, less than 32 MB of memory
On Sat, Jan 19, 2013 at 6:40 PM, Alexander Reichstadt wrote: > Hi, > > I never had any problems to install postgres 9.1, now I do with 9.2. This > is the enterprise installer for Mac OS X. I tried to install and am getting > the dreaded error message regarding shared memory, which is not very > helpful because it does not seem to actually look at the file it complains > about. I edited it, I read the readme. I changed the sizes accordingly, yet > it persists in claiming there to be too less memory on an intel mac pro > with 18 GB of ram. Please, can someone help? > > Thank you in advance > Alex > > Can you try installing again after executing below command as Adminstrator... $ cat /etc/sysctl.conf | sudo xargs sysctl -w Also do share the information requested here... --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] pg_Restore
On Mon, Jan 21, 2013 at 3:01 PM, bhanu udaya wrote: > Hello, > Greetings ! > I tried with all the below options. It approximatly takes 1 hour 30 > minutes for restoring a 9GB database. This much time can not be affordable > as the execution of test cases take only 10% of this whole time and waiting > 1 hour 30 minutes after every test case execution is alot for the > team. Kindly let me know if we can reduce the database restoration time . > > On linux, below settings work well using using -j option of pg_restore. Since its windows, give another try with below option.(as already best suggested in this email). shared_buffers= 1024MB work_mem= 512MB maintenance_work_mem = 1GB checkpoint_segments=(in between 128 - 256) checkpoint_timeout=(default is 15mns make to 1h) autovacuum=off track_counts=off fsync=off full_page_writes=off synchronous_commit=off bgwriter_delay=(default 200ms, change to 50ms) --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > > Thanks and Regards > Radha Krishna > > -- > Date: Mon, 21 Jan 2013 08:15:47 +0100 > Subject: Re: [GENERAL] pg_Restore > From: mag...@hagander.net > To: udayabhanu1...@hotmail.com > CC: franc...@teksol.info; pgsql-general@postgresql.org > > > > On Jan 21, 2013 7:17 AM, "bhanu udaya" wrote: > > > > > > Hello, > > Greetings ! > > Thank you for the prompt reply. I have changed the settings as listed > below: > > > > shared_buffers = 1024MB > > > > work_mem = 512MB > > > > maintenance_work_mem = 512MB > > > > wal_buffers = 100MB > > > fsync = off # ONLY DURING INITIAL DATA LOAD! > > > checkpoint_segments = 128 # large value, such as 128 or 256 (16MB per > file, check disk space) > > > checkpoint_timeout = 30min > > > checkpoint_completion_target = 0.9 > > > wal_level = minimal # You'll need to do a full base backup if you use > this > > But, have same problem. It is almost 1 hour now, the restoration is > still going on. After every test case execution, we would like to refresh > the database and expected refresh should be completed in less than 10 > minutes. Is this achievable with the kind of configuration I have listed in > my earlier email. > > > > Kindly help , as how to speed up this restoration process. > > > > Try running pg_restore with the -1 option. If that doesn't help, try -m4 > or something like that (you'll have to remove the first option then, can't > use both at once) > > But it's going to be pushing it anyway. Your scenario is going to create > thousands of files (assuming you have multiple tables in each of your > schemas as is normal), and that's just not something ntfs does very fast. > Once the files are there, I bet loading the data is reasonably fast since > it can't be all that big > > /Magnus >
Re: [GENERAL] COPY FROM on Windows and accentuated characters in the file path
On Tue, Feb 5, 2013 at 3:47 PM, Thierry Hauchard wrote: > Hi, > > Using COPY FROM doesn't work if the path of the file has accentuated > characters : command doesn't find the file. > COPY FROM 'C:/import/prénoms/datas.txt' is so impossible. > > Is there a turnover ? > > PG 8.4, Windows XP Hmm...seems accent character not recognized by COPY command. postgres=# copy users from 'c:/test/*å*test.txt'; ERROR: could not open file "c:/test/+test.txt" for reading: No such file or directory I guess you should take help of batch script to remove ACCENT characters and your COPY should be good to go. Below link when I googled: http://stackoverflow.com/questions/261515/batch-file-script-to-remove-special-characters-from-filenames-windows Some other's might have better workarounds. --Raghav
[GENERAL] PAM implementation in PG 9.2.3
Hi All, I am trying to implement PAM on my local machine. Below are the details of my setup: OS: RHEL 6 PG: 9.2.3 /etc/pam.d/postgresql (PAM file) #%PAM-1.0 authrequiredpam_unix.so account requiredpam_unix.so $PGDATA/pg_hba.conf # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only local all allpam pamservice=postgresql # IPv4 local connections: hostall all 127.0.0.1/32pam pamservice=postgresql Session 1Session 2 -bash-4.1$ psql -U postgres Password for user postgres: -bash-4.1$ tail -f postgresql-2013-04-26_00.log 2013-04-26 20:08:16.605 IST-13943-postgres-postgres-[local] LOG: pam_authenticate failed: Conversation error 2013-04-26 20:08:16.606 IST-13943-postgres-postgres-[local] FATAL: PAM authentication failed for user "postgres" 2013-04-26 20:08:16.606 IST-13943-postgres-postgres-[local] LOG: could not send data to client: Broken pipe In OS logs(/var/log/secure) Apr 26 20:11:03 localhost res [local] authentication: pam_unix(postgresql:auth): conversation failed Apr 26 20:11:03 localhost res [local] authentication: pam_unix(postgresql:auth): auth could not identify password for [postgres] I have not entered password in session 1 which was waiting for my password to enter, but logs are printed beforehand in both logs (OS/DB) as you see in session 2. When, I enter password in session 1 which was waiting for password, it will immediately takes me into the database without any error. Couple of question's in mind on this scenario: 1. Why database server logs shows lines beforehand as pam_authentication failed even though I have not typed password ? 2. Though logs show as PAM authentication failed, still user's are allowed into the database ? 3. What above log entries mean precisely ? 4. Was my PAM setup successful ? Sorry for my too many questions, I just lost all of my endeavors to understand the above scenario. Any help will be highly appreciated. Thanks in advance. --- Regards, Raghavendra
Re: [GENERAL] PAM implementation in PG 9.2.3
On Wed, May 8, 2013 at 4:55 PM, Amit Langote wrote: > Hello Raghavendra, > > I think probably psql is a problem here. WIthout -W (force password > before connect) option psql has no way knowing if this user needs a > password to connect to the given database. So, psql first attempts a > connection to the database without a password (if -W is not > specified), which fails since server responds with "password needed" > kind of message back to psql (this is because we have set 'pam' as the > authentication method). This makes psql know that a password is needed > for this user/database combination and it prompts for the password and > subsequently connects successfully if correct password is specified. > But this first unsuccessful attempt is indeed logged by the server as > authentication failure just as what you see. So, this logged failure > is just the kind of dummy connection attempt (indeed withoutn > password) made by the psql. > > Firstly, Thank you for your insight explanation. > However, if you specify -W option, psql won't connect before it > accepts password. You can try this (and see that no authentication > failure is logged) > Affirmative, I have tried with -W option and it worked as expected and authentication passed as per PAM setup. However, PG documentation doesn't highlight about this in psql or PAM section, because log entries written are slightly confusing. http://www.postgresql.org/docs/9.2/static/auth-methods.html http://www.postgresql.org/docs/9.2/static/app-psql.html I think log entries just mean the authentication has failed with > PAM-specific error message. > > Yep... understood. --- Regards, Raghavendra Blog: http://raghavt.blogspot.com/ > -- > > Amit Langote >
Re: [GENERAL] Views
On Thu, May 9, 2013 at 4:33 PM, itishree sukla wrote: > Hi all, > > Is there any way, i can know what all views are there on a table by a sql > query? > > > Regards, > Itishree > Try this... select table_name,view_name from information_schema.view_table_usage where table_name='table_name'; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] PAM implementation in PG 9.2.3
On Mon, May 13, 2013 at 11:17 AM, Amit Langote wrote: > > However, PG documentation doesn't highlight about this in psql or PAM > > section, because log entries written are slightly confusing. > > http://www.postgresql.org/docs/9.2/static/auth-methods.html > > http://www.postgresql.org/docs/9.2/static/app-psql.html > > It turns out this logging is a bug in PAM authentication code in the > backend that needs to resolved. This has been reported on > pgsql-hackers. > > > Thank you. --Raghav
[GENERAL] pg_basebackup -R option in PG 9.3 beta
Hi, While trying new features of Pg 9.3 beta, my eyes caught on pg_basebackup -R option so I have given a try but somehow I didn't succeed it to work... -bash-4.1$ ./pg_basebackup --version pg_basebackup (PostgreSQL) 9.3beta1 -bash-4.1$ ./pg_basebackup -h localhost -D ../data_bkp -p -U postgres -R ./pg_basebackup: symbol lookup error: ./pg_basebackup: undefined symbol: PQconninfo Can anyone help me where am doing the mistake ? Document says it write's recovery.conf file in the output directory. http://www.postgresql.org/docs/9.3/static/app-pgbasebackup.html Thanks in advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] pg_basebackup -R option in PG 9.3 beta
On Wed, May 15, 2013 at 3:18 PM, Hari Babu wrote: > On Wednesday, May 15, 2013 2:50 PM Raghavendra wrote: > > >Hi, > > >** ** > > >While trying new features of Pg 9.3 beta, my eyes caught on > pg_basebackup -R option so I have given a try but somehow I didn't succeed > it to work... > > >** ** > > >-bash-4.1$ ./pg_basebackup --version > > >pg_basebackup (PostgreSQL) 9.3beta1 > > >** ** > > >-bash-4.1$ ./pg_basebackup -h localhost -D ../data_bkp -p -U > postgres -R > > >./pg_basebackup: symbol lookup error: ./pg_basebackup: undefined symbol: > PQconninfo > > >** ** > > >Can anyone help me where am doing the mistake ? > > ** ** > > I feel the library is not able to load while executing pg_basebackup. > > Try the following command in linux and check it. > > ** ** > > export LD_LIBRARY_PATH= > > ** ** > > Regards, > > Hari babu. > Thank you for your quick response. It was my oversight. It worked. In earlier version of primary connection information was enclosed within one single quote: primary_conninfo = 'user=postgres host=localhost port=5432' But, the file created with -R option (recovery.conf) has doubled the single quotes for all variable values user/host/port/sslmode/sslcompression in primary connection. standby_mode = 'on' primary_conninfo = 'user=''postgres'' host=''localhost'' port='''' sslmode=''disable'' sslcompression=''1'' ' Was it intentional to double the singe quotes of variable values ? --Raghav
Re: [GENERAL] pg_basebackup -R option in PG 9.3 beta
On Wed, May 15, 2013 at 4:26 PM, Hari Babu wrote: > >on Wednesday, May 15, 2013 4:04 PM Raghavendra wrote: > > >In earlier version of primary connection information was enclosed within > one single quote: > > >primary_conninfo = 'user=postgres host=localhost port=5432' > > >** ** > > >But, the file created with -R option (recovery.conf) has doubled the > single quotes for all variable values > user/host/port/sslmode/sslcompression in primary connection. > > >** ** > > >standby_mode = 'on' > > >primary_conninfo = 'user=''postgres'' host=''localhost'' port='''' > sslmode=''disable'' sslcompression=''1'' ' > > >** ** > > >Was it intentional to double the singe quotes of variable values ? > > ** ** > > Yes, It was intentional to support some of the special characters in > configuration parameters which can be used in the primary_conninfo. > > ** ** > > There is a patch in the next commit-fest which is further extending the > special characters in password. > > Please refer the following link. > > ** ** > > https://commitfest.postgresql.org/action/patch_view?id=1085 > > ** ** > > Regards, > > Hari babu. > > ** ** > Thank you. --Raghav
Re: [GENERAL] Schema (Search path issue) on PostgreSQL9.2
> postgres=# select version(); > version > > > --- > PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 > 20080704 (Red Hat 4.1.2-52), 64-bit > (1 row) > > postgres=# \dn > List of schemas > Name| Owner > +-- > information_schema | postgres > pg_catalog | postgres > pg_toast | postgres > pg_toast_temp_1| postgres > public | postgres > (5 rows) > > Apart from your actual question, am just curious to see this output, how come all schema's displayed whereas in latest releases only PUBLIC schema will be displayed if you use meta command \dn. -bash-4.1$ ./psql -p psql (9.3beta1) Type "help" for help. postgres=# \dn List of schemas Name | Owner +-- public | postgres (1 row) --Raghav
Re: [GENERAL] Regarding Postgres Plus Associate Certification
On Fri, May 17, 2013 at 1:16 AM, Oscar Calderon < ocalde...@solucionesaplicativas.com> wrote: > Hi to everybody, i just wanna ask you if somebody that already has the > certification or if is studying to get it knows if there's a book (or a set > of books) that you recommend me that contains most of the necessary > information to cover the topics of the certification, that are the next: > >- PostgreSQL System Architecture >- Installation >- Configuration >- Creating and Managing Databases >- Introduction to PSQL >- pgAdmin III >- Security basics >- SQL >- Backup and Recovery >- Point-in Time Recovery >- Routine Maintenance >- Postgres Data Dictionary >- Moving Data > > Or if the documentation is enough to cover those topics, because i want to > study to get the certification. > > Its a basic exam to test your knowledge and skills on the topics. All above topic's are well covered in PostgreSQL documentation, or you can also refer to PostgreSQL Administration Cookbook. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Table Partitioning
On Tue, May 21, 2013 at 11:03 PM, Richard Onorato wrote: > I am wanting to partition my data based on a mod of one of the bigint > columns, but when I run my insert test all the data goes into the base > table and not the partitions. Here is what the table looks like: > > CREATE table MyMappingTable ( id bigserial NOT NULL, > c1 bigInt NOT NULL, > c2 bigInt NOT NULL, > c3 bigint NOT NULL, > count bigint DEFAULT 1, > createdTime timestamp with time zone > default CURRENT_TIMESTAMP, > CONSTRAINT MyMappingTable_index > PRIMARY KEY (id) ) > with (OIDS=FALSE); > > CREATE TABLE MyMappingTableT1 (PRIMARY KEY (id), CHECK((c1 % 5) = 0)) > INHERITS (MyMappingTable); > CREATE TABLE MyMappingTableT2 (PRIMARY KEY (id), CHECK((c1 % 5) = 1)) > INHERITS (MyMappingTable); > CREATE TABLE MyMappingTableT3 (PRIMARY KEY (id), CHECK((c1 % 5) = 2)) > INHERITS (MyMappingTable); > CREATE TABLE MyMappingTableT4 (PRIMARY KEY (id), CHECK((c1 % 5) = 3)) > INHERITS (MyMappingTable); > CREATE TABLE MyMappingTableT5 (PRIMARY KEY (id), CHECK((c1 % 5) = 4)) > INHERITS (MyMappingTable); > > Here is the trigger function that I added to the database: > > CREATE OR REPLACE FUNCTION my_mapping_table_insert_trigger() > RETURNS trigger AS $$ > BEGIN > IF ( (NEW.c1 % 5) = 0 ) THEN > INSERT INTO MyMappingTableT1 VALUES (NEW.*); > ELSIF ( (NEW.c1 % 5) = 1 ) THEN > INSERT INTO MyMappingTableT2 VALUES (NEW.*); > ELSIF ( (NEW.c1 % 5) = 2 ) THEN > INSERT INTO MyMappingTableT3 VALUES (NEW.*); > ELSIF ( (NEW.c1 % 5) = 3 ) THEN > INSERT INTO MyMappingTableT4 VALUES (NEW.*); > ELSIF ( (NEW.c1 % 5) = 4 ) THEN > INSERT INTO MyMappingTableT5 VALUES (NEW.*); > ELSE > RAISE EXCEPTION 'c1 mod out of range. Something wrong with > the my_mapping_table_insert_trigger() function!'; > END IF; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > Here is the Trigger that I added to the table: > > CREATE TRIGGER insert_my_mapping_table_trigger > BEFORE INSERT ON MyMappingTable > FOR EACH ROW EXECUTE PROCEDURE my_mapping_table_insert_trigger(); > > SET constraint_exclusion = ON; > > Regards, > > Richard > I tried your test case, its working fine from my end and populating data properly to partition childs. insert into mymappingtable values (1,7,20,30,1,now()); insert into mymappingtable values (2,6,20,30,1,now()); insert into mymappingtable values (3,8,20,30,1,now()); insert into mymappingtable values (4,9,20,30,1,now()); insert into mymappingtable values (5,10,20,30,1,now()); postgres=# \dt+ MyMappingTable* List of relations Schema | Name | Type | Owner |Size| Description +--+---+--++- public | mymappingtable | table | postgres | 0 bytes| public | mymappingtablet1 | table | postgres | 8192 bytes | public | mymappingtablet2 | table | postgres | 8192 bytes | public | mymappingtablet3 | table | postgres | 8192 bytes | public | mymappingtablet4 | table | postgres | 8192 bytes | public | mymappingtablet5 | table | postgres | 8192 bytes | --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Table Partitioning
On Wed, May 22, 2013 at 6:54 AM, Richard Onorato wrote: > Were you able to get it to insert with the bigserial being used on the > table? > Yes. > Every time I go to do an insert into one of the inherited tables I am now > getting the following exception: > org.hibernate.HibernateException: The database returned no natively > generated identity value > > Hmm, I guess you are inserting on the parent table not directly into inherited table. Can you share the INSERT statement. > Is auto-increment supported on table partitioning? > > Yes, BIGSERIAL will create a sequence that will be shared by all child partitions. Check below example as per your test case, INSERT statement do not have BIGSERIAL column still its auto-increment and populated data in child tables. postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (9,20,30,1,now()); INSERT 0 0 postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (7,20,30,1,now()); INSERT 0 0 postgres=# select * from mymappingtablet5; id | c1 | c2 | c3 | count | createdtime ++++---+-- 8 | 9 | 20 | 30 | 1 | 2013-05-18 02:08:33.061548+05:30 postgres=# select * from mymappingtablet3; id | c1 | c2 | c3 | count | createdtime ++++---+-- 9 | 7 | 20 | 30 | 1 | 2013-05-18 02:12:03.076529+05:30 (1 row) (Request not to top-post please ... :) ) --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Interrupt WAL recovery
On Wed, May 22, 2013 at 3:38 PM, Fabio Rueda Carrascosa < avances...@gmail.com> wrote: > hello , I'm restoring a 7 days ago full backup + wal files. I have to > recover more than 6k wal files and I have no time, I would accept to work > with a ~ 3 days ago snapshot, can I stop the recovery process and start the > server?, if yes, how? > > My recovery.conf file only has this line : > > restore_command = 'envdir /etc/wal-e.d/env /etc/wal-e.d/wal-e wal-fetch > "%f" "%p"' > > Thanks in advance. > You can control the recovery process with recovery_target_time parameter. This parameter helps you to recover wals till to the acceptable time for you from the snapshot. http://www.postgresql.org/docs/9.2/static/recovery-target-settings.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Interrupt WAL recovery
On Wed, May 22, 2013 at 3:53 PM, Fabio Rueda Carrascosa < avances...@gmail.com> wrote: > Can I stop the server and modify recovery.conf with this param and restart > it again without problems? > > It should work. (Sorry I never tried this, like stopping in middle of the recovery and restarting with some changes. Sure there will be answer's here if someone has more insight on this scenario) Its good to include this parameter in recovery.conf at the recovery start time. -- Raghav > > 2013/5/22 Raghavendra > >> On Wed, May 22, 2013 at 3:38 PM, Fabio Rueda Carrascosa < >> avances...@gmail.com> wrote: >> >>> hello , I'm restoring a 7 days ago full backup + wal files. I have to >>> recover more than 6k wal files and I have no time, I would accept to work >>> with a ~ 3 days ago snapshot, can I stop the recovery process and start the >>> server?, if yes, how? >>> >>> My recovery.conf file only has this line : >>> >>> restore_command = 'envdir /etc/wal-e.d/env /etc/wal-e.d/wal-e wal-fetch >>> "%f" "%p"' >>> >>> Thanks in advance. >>> >> >> You can control the recovery process with recovery_target_time parameter. >> This parameter helps you to recover wals till to the acceptable time for >> you from the snapshot. >> >> http://www.postgresql.org/docs/9.2/static/recovery-target-settings.html >> >> --- >> Regards, >> Raghavendra >> EnterpriseDB Corporation >> Blog: http://raghavt.blogspot.com/ >> >> >
Re: [GENERAL] Interrupt WAL recovery
On Wed, May 22, 2013 at 4:15 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Wed, May 22, 2013 at 3:53 PM, Fabio Rueda Carrascosa < > avances...@gmail.com> wrote: > >> Can I stop the server and modify recovery.conf with this param and >> restart it again without problems? >> >> > It should work. (Sorry I never tried this, like stopping in middle of the > recovery and restarting with some changes. Sure there will be answer's here > if someone has more insight on this scenario) > > Its good to include this parameter in recovery.conf at the recovery start > time. > > Just did some testing, two ways found: 1. You can stop the server and add the recovery_target_time in recovery.conf file to current_time + 1 hr, so that recovery completes consistently till to the specified time and open the server for R/W. 2. You can stop the server and rename the recovery.conf to recovery.done and start the server assuming it has reached to consistent state by this way it opens the server from that point for R/W. --Raghav > -- Raghav > > > > >> >> 2013/5/22 Raghavendra >> >>> On Wed, May 22, 2013 at 3:38 PM, Fabio Rueda Carrascosa < >>> avances...@gmail.com> wrote: >>> >>>> hello , I'm restoring a 7 days ago full backup + wal files. I have to >>>> recover more than 6k wal files and I have no time, I would accept to work >>>> with a ~ 3 days ago snapshot, can I stop the recovery process and start the >>>> server?, if yes, how? >>>> >>>> My recovery.conf file only has this line : >>>> >>>> restore_command = 'envdir /etc/wal-e.d/env /etc/wal-e.d/wal-e wal-fetch >>>> "%f" "%p"' >>>> >>>> Thanks in advance. >>>> >>> >>> You can control the recovery process with recovery_target_time >>> parameter. This parameter helps you to recover wals till to the acceptable >>> time for you from the snapshot. >>> >>> http://www.postgresql.org/docs/9.2/static/recovery-target-settings.html >>> >>> --- >>> Regards, >>> Raghavendra >>> EnterpriseDB Corporation >>> Blog: http://raghavt.blogspot.com/ >>> >>> >> >
Re: [GENERAL] How to evaluate disk space needed by a table
On Tue, May 28, 2013 at 9:48 AM, 高健 wrote: > Hello: > > I created a table, and found the file created for that table is about 10 > times of that I estimated! > The following is what I did: > > postgres=# create table tst01(id integer); > CREATE TABLE > postgres=# > > postgres=# select oid from pg_class where relname='tst01'; > oid > --- > 16384 > (1 row) > Then I can see the file now: > [root@lex base]# ls ./12788/16384 > ./12788/16384 > > I heard that one integer type will use 4 bytes. > so I think that 2048 records with only one column of integer data type, > will use a little more than 8K(2048 records * 4 bytes/per integer data > type + headers). > > You heard right, as other said there are various hidden fileds added to every tuple like (ctid,xmin,xmax,cmin,cmax). All these occupy some bytes in the page. Take your example. As per integer column, every column data occupies 4 bytes. postgres=# select pg_column_size(id) from tst01 limit 1; pg_column_size 4 (1 row) When you calculate the row size... postgres=# select pg_column_size(t) from tst01 t limit 1; pg_column_size 28 (1 row) Here 24 bytes as row header and 4 bytes of integer data. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] updatable view
On Tue, Jun 11, 2013 at 3:32 AM, Sajeev Mayandi wrote: > Hi, > > I am using 9.2. The documentation says that there is no support for > updatable view. It suggesting to use the triggers or rule. Can somebody > paste the sample code for the same. > > Very well summarized by Craig on SO with links (code/implementation). Below link helps you about "how to" on the updatable views. http://stackoverflow.com/questions/13151566/cannot-update-view --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] autovacuum: found orphan temp table
On Tue, Jun 25, 2013 at 6:43 PM, Nicolau Roca wrote: > Hi, > after a server crash the following messages appear in the log file every > minute: > > 2013-06-25 15:02:15 CEST [::18264:1:] LOG: autovacuum: found orphan temp > table "pg_temp_47"."est_backup_ids_temp" in database "estudis1314" > 2013-06-25 15:02:15 CEST [::18264:2:] LOG: autovacuum: found orphan temp > table "pg_temp_47"."est_backup_files_temp" in database "estudis1314" > > I read a suggestion in the list pgsql-hackers (Message ID > 48f4599d.7010...@enterprisedb.com<http://www.postgresql.org/message-id/48f4599d.7010...@enterprisedb.com>) > about just dropping the pg_temp_x schema. However, no such schema exists: > > You can query to find those schemas : select relname,nspname from pg_class join pg_namespace on (relnamespace= pg_namespace.oid) where pg_is_other_temp_schema(relnamespace); On finding you can drop those schemas,if you want to get rid of the messages, just do DROP SCHEMA pg_temp_NNN CASCADE; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Re: [GENERAL] 回复： [GENERAL] Can't create plpython language
> > > [postgres@lix PostgreSQL]$ psql > Password: > psql (9.2.4) > Type "help" for help. > > postgres=# create language plpython2u; > ERROR: could not access file "$libdir/plpython2": No such file or > directory > postgres=# create language plpython3u; > ERROR: could not access file "$libdir/plpython3": No such file or > directory > postgres=# \q > > There are python 2.4.3 and python3.2 on the same machine,. > I tried to create it using ActivePython3.2. It worked. Stop the running postgresql cluster and set the ActivePython3.2 path as below. export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH export PYTHONHOME=/opt/ActivePython-3.2/ export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib/ Now start the postgresql cluster and try creating the language; -bash-4.1$ psql psql.bin (9.2.3) Type "help" for help. postgres=# create language plpython3u; CREATE LANGUAGE postgres=# --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > -- 原始邮件 -- > *发件人:* "Jov"; > *发送时间:* 2013年7月5日(星期五) 上午7:59 > *收件人:* "Michael Paquier"; ** > *抄送:* ""; "pgsql-general"< > pgsql-general@postgresql.org>; ** > *主题:* Re: [GENERAL] Can't create plpython language > > try the postgresql official rpm package for centos 5： > > http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm > > or build your own from source. > > Jov > blog: http:amutu.com/blog <http://amutu.com/blog> > > > 2013/7/5 Michael Paquier > >> On Thu, Jul 4, 2013 at 6:26 PM, guxiaobo1982 wrote: >> > Hi, >> > >> > I am running PostgreSQL 9.2.4.1 (from EnterpriseDB) on CENTOS 5.9 X64 >> > server, I got the following errors when trying to create the python >> language >> > handler, can you help with this, thanks in advance. >> If this is PPAS, as the version number would suggest, you should >> directly ask this question to EDB support team. This problem might not >> be related to Postgres itself... At least the buildfarm machines do >> not crash when trying to install a plpython extension. >> -- >> Michael >> >> >> -- >> 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] function with unknown params
On Tue, Jul 9, 2013 at 10:00 PM, giozh wrote: > i've declared and implemented e function like: > > CREATE OR REPLACE FUNCTION acquistoLotto(m_g INTEGER, grossista > VARCHAR(20), > produttore BIGINT, costo INTEGER, dat DATE, descr VARCHAR(120), num_prod > INTEGER) RETURNS VOID AS $$ > > but when i'm trying to call it like > > select acquistoLotto(0, 'grossista', 52187073424, 10, 22/1/2013, 'ciao ciao > ciao', 10); > > Seems you missing on how to pass date field. Try this it will work. postgres=# select acquistoLotto(0, 'grossista', 52187073424, 10, '1-22-2013', 'ciao ciaociao', 10); acquistolotto --- (1 row) or postgres=# select acquistoLotto(0, 'grossista', 52187073424, 10, '1-22-2013'::date, 'ciao ciaociao', 10); acquistolotto --- (1 row) --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language
On Wed, Jul 10, 2013 at 7:36 AM, guxiaobo1982 wrote: > I am using PostgreSQL 9.3 beta 2 > > [postgres@lix Multicorn-master]$ psql > psql (9.3beta2) > Type "help" for help. > > postgres=# > > > It does not work with > ActivePython3.2(ActivePython-3.2.2.3-linux-x86_64.tar.gz), Which version of > PostgreSQL are you running? > > Sorry for being late on this email. I overlooked the version you were pointing. I didn't test on PG 9.3Beta, I did on PG 9.2 after looking your error showing PG 9.2.4 [postgres@lix PostgreSQL]$ psql > Password: > psql (9.2.4) > Type "help" for help. > postgres=# create language plpython2u; > ERROR: could not access file "$libdir/plpython2": No such file or > directory > postgres=# create language plpython3u; > ERROR: could not access file "$libdir/plpython3": No such file or > directory Later, I began testing it with PG 9.3beta and encountered same error message as you have shared here. Did some analysis and finally succeeded to create language plpython3u with AP-3.2, however with not many tweaks in compilation.(Steps might be arguing. Pardon me). Despite the fact that I have ActivePython-3.2 on my system, source compilation was looking for shared library. Hence compiled explicitly with shared_libpython=yes. Pre-Steps: (Assuming you have ActivePython 3.2) cd /opt/ActivePython-3.2/bin/ cp python3.2-config python-config cp python3. python Test: export PATH=/opt/ActivePython-3.2/bin:$PATH # which python /opt/ActivePython-3.2/bin/python # which python-config /opt/ActivePython-3.2/bin/python-config Steps: 1. Install PG 9.3 beta with below steps: export PATH=/opt/ActivePython-3.2/bin:$PATH ./configure --prefix=/usr/local/pg93b3 --with-python make shared_libpython=yes make shared_libpython=yes install 2. After installation you should see below files in your PG installation Path: [root@localhost pg93b3]# pwd /usr/local/pg93b3 [root@localhost pg93b3]# find . | grep python ./lib/postgresql/plpython3.so ./share/postgresql/extension/plpython3u--unpackaged--1.0.sql ./share/postgresql/extension/plpython3u--1.0.sql ./share/postgresql/extension/plpython3u.control 3. Now, create new cluster using INITDB 4. Before starting the cluster set the PYTHONHOME, PYTHONPATH, LD_LIBRARY_PATH export PYTHONHOME=/opt/ActivePython-3.2/ export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib:$LD_LIBRARY_PATH 5. Start the cluster and create the language: /usr/local/pg93b3/bin/pg_ctl -D /usr/local/pg93b3/data/ start -bash-4.1$ psql -p psql (9.3beta1) Type "help" for help. postgres=# create language plpython3u; CREATE LANGUAGE Out of my analysis on the issue, Asif Naeem from our Dev group shared his valuable thoughts to conclude this. Thanks Asif. Question still in mind, Why plpython depends on Shared Libraries (.so) ? http://www.postgresql.org/docs/9.3/static/install-requirements.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] How can you get "WAL segment has already been removed" when doing synchronous replication ?!
On Thu, Jul 11, 2013 at 11:18 PM, hubert depesz lubaczewski < dep...@depesz.com> wrote: > We are seeing situation like this: > 1. 9.2.4 database > 2. Master settings: >name|setting > ---+--- > fsync | on > synchronize_seqscans | on > synchronous_commit| remote_write > synchronous_standby_names | * > wal_sync_method | open_datasync > (5 rows) > > Yet, every now and then we're getting: > FATAL: requested WAL segment * has already been removed > > Assuming no part of the system is issuing "set synchronous_commit > = off", how can we get in such situation? > > Best regards, > > depesz > > Increasing the wal_keep_segments ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Full text search
On Fri, Jul 12, 2013 at 11:48 AM, itishree sukla
wrote:
> Hello everyone,
>
> I am using full text search, however it is not helping me to get the
> partial value.
>
> For example in my document let Pizza Hut is there, if i am searching for
> Pizza Hut is it giving me the values for only Pizza or a spell mistake like
> pizz is not returning any thing. any work around for this, please suggest.
>
> Regards,
> Itishree
>
Hope you are looking like this.
create table foo(v text);
insert into foo values('Near to my house there\'s no any Pizza Hut
restuarant');
insert into foo values('I wont like pizza, but friends are crazy of it');
postgres=# select * from foo where v ~* 'pizz';
v
--
Near to my house there's no any Pizza Hut restuarant
I wont like pizza, but friends are crazy of it
(2 rows)
postgres=# select * from foo where v ~* 'pizza hut';
v
--
Near to my house there's no any Pizza Hut restuarant
(1 row)
postgres=# select * from foo where v ~* 'pizza';
v
--
Near to my house there's no any Pizza Hut restuarant
I wont like pizza, but friends are crazy of it
(2 rows)
or
with ILIKE
select * from foo where v ilike '%hut%';
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] function returning select result to JDBC
On Fri, Jul 12, 2013 at 3:26 PM, giozh wrote: > I need to write a function (invoked by a jdbc client) that returns select > query result. > That function could return also set of row. How should i declare return > type > of function? > > > Bunch of examples here: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/function-returning-select-result-to-JDBC-tp5763565.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 >
[GENERAL] Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language
On Mon, Jul 15, 2013 at 7:10 PM, guxiaobo1982 wrote: > It works with ActivePython 3.2.2.3, > Thanks for notifying. > but not ActivePython 2.7.2.5. > > No idea... :), I tried AP 3.2 with PG 9.2 & PG 9.3B it went fine for me. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > > -- Original ------ > *From: * "Raghavendra"; > *Date: * Jul 11, 2013 > *To: * "guxiaobo1982"; ** > *Cc: * "Jov"; "Michael Paquier"; > "pgsql-general"; "Asif Naeem"< > asif.na...@enterprisedb.com>; ** > *Subject: * Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython > language > > On Wed, Jul 10, 2013 at 7:36 AM, guxiaobo1982 wrote: > >> I am using PostgreSQL 9.3 beta 2 >> >> [postgres@lix Multicorn-master]$ psql >> psql (9.3beta2) >> Type "help" for help. >> >> postgres=# >> >> >> It does not work with >> ActivePython3.2(ActivePython-3.2.2.3-linux-x86_64.tar.gz), Which version of >> PostgreSQL are you running? >> >> > Sorry for being late on this email. I overlooked the version you were > pointing. I didn't test on PG 9.3Beta, I did on PG 9.2 after looking your > error showing PG 9.2.4 > > [postgres@lix PostgreSQL]$ psql >> Password: >> psql (9.2.4) >> Type "help" for help. >> postgres=# create language plpython2u; >> ERROR: could not access file "$libdir/plpython2": No such file or >> directory >> postgres=# create language plpython3u; >> ERROR: could not access file "$libdir/plpython3": No such file or >> directory > > > Later, I began testing it with PG 9.3beta and encountered same error > message as you have shared here. > Did some analysis and finally succeeded to create language plpython3u with > AP-3.2, however with not many tweaks in compilation.(Steps might be > arguing. Pardon me). > > Despite the fact that I have ActivePython-3.2 on my system, source > compilation was looking for shared library. Hence compiled explicitly with > shared_libpython=yes. > > Pre-Steps: (Assuming you have ActivePython 3.2) > > cd /opt/ActivePython-3.2/bin/ > cp python3.2-config python-config > cp python3. python > > Test: > export PATH=/opt/ActivePython-3.2/bin:$PATH > # which python > /opt/ActivePython-3.2/bin/python > # which python-config > /opt/ActivePython-3.2/bin/python-config > > Steps: > > 1. Install PG 9.3 beta with below steps: > > export PATH=/opt/ActivePython-3.2/bin:$PATH > ./configure --prefix=/usr/local/pg93b3 --with-python > make shared_libpython=yes > make shared_libpython=yes install > > 2. After installation you should see below files in your PG installation > Path: > > [root@localhost pg93b3]# pwd > /usr/local/pg93b3 > [root@localhost pg93b3]# find . | grep python > ./lib/postgresql/plpython3.so > ./share/postgresql/extension/plpython3u--unpackaged--1.0.sql > ./share/postgresql/extension/plpython3u--1.0.sql > ./share/postgresql/extension/plpython3u.control > > 3. Now, create new cluster using INITDB > 4. Before starting the cluster set the PYTHONHOME, PYTHONPATH, > LD_LIBRARY_PATH > > export PYTHONHOME=/opt/ActivePython-3.2/ > export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH > export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib:$LD_LIBRARY_PATH > > 5. Start the cluster and create the language: > > /usr/local/pg93b3/bin/pg_ctl -D /usr/local/pg93b3/data/ start > > -bash-4.1$ psql -p > psql (9.3beta1) > Type "help" for help. > > postgres=# create language plpython3u; > CREATE LANGUAGE > > Out of my analysis on the issue, Asif Naeem from our Dev group shared his > valuable thoughts to conclude this. Thanks Asif. > > Question still in mind, Why plpython depends on Shared Libraries (.so) ? > > http://www.postgresql.org/docs/9.3/static/install-requirements.html > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ >
[GENERAL] Re: Reply: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language
Foremost, its interesting, why your replies populate as distinct thread rather than of continuing one. (I may be incorrect, are you choosing "forward" option while replying, no ?). Request to hold the continuity of the post instead of different chunks for each reply. :) On Thu, Jul 18, 2013 at 12:04 PM, guxiaobo1982 wrote: > Problem with ActivePython-2.7.2.5 is > No. It compiles well with this version too. This time I have not tweaked ActivePython binary files like I did for AP 3.2, I have just enabled Active Python 2.7 in my path while compiling source. export PATH=/opt/ActivePython-2.7/bin:$PATH ./configure --prefix=/usr/local/pg93b2 --with-python make shared_libpython=yes make shared_libpython=yes install export PYTHONHOME=/opt/ActivePython-2.7/ export PYTHONPATH=/opt/ActivePython-2.7/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pg93b2/lib export LD_LIBRARY_PATH=/opt/ActivePython-2.7/lib:$LD_LIBRARY_PATH -bash-4.1$ ./psql -p psql (9.3beta2) Type "help" for help. postgres=# postgres=# create language plpython2u; CREATE LANGUAGE By chance if you have missed this part of the PG documentation reading, then please go through it, it has clear instructions of how you enable Plpython2 or Plpython3. In particular, the tip section http://www.postgresql.org/docs/9.3/static/plpython-python23.html "*Tip:* The built variant depends on which Python version was found during the installation or which version was explicitly set using the PYTHON environment variable; see Section 15.4<http://www.postgresql.org/docs/9.3/static/install-procedure.html>. To make both variants of PL/Python available in one installation, the source tree has to be configured and built twice." > [postgres@lix stado]$ psql > psql (9.3beta2) > Type "help" for help. > > postgres=# create language plpython2u; > ERROR: could not load library > "/opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so": > /opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so: undefined symbol: > PyObject_Bytes > > Not certain on the error. Retake the test with above steps you should succeed. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > with environment variables as > > export PGDATA=/opt/PostgreSQL/pgdata/93b2src > export PYTHONHOME=/opt/ActivePython-2.7 > export PYTHONPATH=/opt/ActivePython-2.7/bin > > export PATH=/opt/PostgreSQL/93b2src/bin:$PYTHONHOME/bin:$PATH > export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH > > > > > > -- Original -- > *Sender:* "Raghavendra"; > *Send time:* Tuesday, Jul 16, 2013 4:53 PM > *To:* "guxiaobo1982"; ** > *Cc:* "Jov"; "Michael Paquier"; > "pgsql-general"; "Asif Naeem"< > asif.na...@enterprisedb.com>; ** > *Subject:* Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython > language > > On Mon, Jul 15, 2013 at 7:10 PM, guxiaobo1982 wrote: > >> It works with ActivePython 3.2.2.3, >> > > Thanks for notifying. > > >> but not ActivePython 2.7.2.5. >> >> > No idea... :), I tried AP 3.2 with PG 9.2 & PG 9.3B it went fine for me. > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > > > > > > > >> >> -- Original -- >> *From: * "Raghavendra"; >> *Date: * Jul 11, 2013 >> *To: * "guxiaobo1982"; ** >> *Cc: * "Jov"; "Michael Paquier"< >> michael.paqu...@gmail.com>; "pgsql-general"; >> "Asif Naeem"; ** >> *Subject: * Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython >> language >> >> On Wed, Jul 10, 2013 at 7:36 AM, guxiaobo1982 wrote: >> >>> I am using PostgreSQL 9.3 beta 2 >>> >>> [postgres@lix Multicorn-master]$ psql >>> psql (9.3beta2) >>> Type "help" for help. >>> >>> postgres=# >>> >>> >>> It does not work with >>> ActivePython3.2(ActivePython-3.2.2.3-linux-x86_64.tar.gz), Which version of >>> PostgreSQL are you running? >>> >>> >> Sorry for being late on this email. I overlooked the version you were >> pointing. I didn't test on PG 9.3Beta, I did on PG 9.2 after looking your >> error showing PG 9.2.4 >> >> [postgres@lix PostgreSQL]$ psql >>> Password: >>> psql (9.2.4) >>> Type "help" for help. >>> postgres=# create language plpython2u; >>> ERROR: could not access file "$libdir/plpython2": No such file or >>> directory >>> postgres=#
[GENERAL] Re: [GENERAL] Re: Reply: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language
On Fri, Jul 19, 2013 at 1:30 PM, John R Pierce wrote: > On 7/19/2013 12:12 AM, Raghavendra wrote: > >> ... its interesting, why your replies populate as distinct thread rather >> than of continuing one >> > > the mailer they are using isn't generating 'In-Reply-To' and/or > 'References' headers. > > > Oh Ok. Thank you for elucidating. --Raghav
Re: [GENERAL] how to get UPDATEXML function in postgresql as it works in oracle
On Mon, Jul 29, 2013 at 7:14 PM, saritha N wrote:
> Hi,
>
> I am new to postgresql.We are migrating our application from oracle to
> postgresql.We are using postgresql version 9.2.All most everything we are
> migrated but I am unable to write a function for UPDATEXML which works same
> as in oracle.Please help me to resolve .
>
> Are you looking in this way
create table xdata(id int,xmlcode xml);
insert into xdata values (1,'Infosys');
insert into xdata values (1,'Enterprisedb');
insert into xdata values (1,'Wipro');
postgres=# select * from xdata ;
id |xmlcode
+---
1 | Infosys
1 | Enterprisedb
1 | Wipro
(3 rows)
postgres=# update xdata set xmlcode='Infosys-Bangalore'
where cast(xpath('//values/text()',xmlcode) as text[]) = '{Infosys}';
UPDATE 1
postgres=# select * from xdata ;
id | xmlcode
+
1 | Enterprisedb
1 | Wipro
1 | Infosys-Bangalore
(3 rows)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] how to get UPDATEXML function in postgresql as it works in oracle
On Tue, Jul 30, 2013 at 9:51 AM, saritha N wrote:
> Thanks for your reply Raghavendra,
>
>
Thanks for the update.
Request to mark postgresql group email while replying so it will help much
if other's have better idea as well if any correction in my test case.
> Whatever you have sent its very useful for me.I need to add one more node
> between the values,like
> InfosysBangalore
> How to add?
>
>
postgres=# update xdata set
xmlcode='InfosysBangalore' where
cast(xpath('//values/text()',xmlcode) as text[]) = '{Infosys}';
UPDATE 1
postgres=# select xmlparse(content xmlcode) from xdata ;
xmlparse
--
Enterprisedb
Wipro
InfosysBangalore
(3 rows)
Some of the xml related links:
http://www.postgresql.org/docs/9.2/static/functions-xml.html
http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#Ensure_that_xpath.28.29_escapes_special_characters_in_string_values
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Populating array of composite datatype
On Wed, Aug 7, 2013 at 4:04 PM, Sameer Thakur wrote:
> Hello,
> I have a composite datatype abc which has two integer fields x,y.
> I have a table Test which has an array of abc.
> I am trying to populate Test. Tried
> insert into test values (ARRAY[abc(1,2)]); but got error
> ERROR: function abc(integer, integer) does not exist
>
> Is there anyway for doing this?
>
>
I think you need to use row() and explicit type cast.
http://www.postgresql.org/docs/9.2/static/rowtypes.html
postgres=# create type abc as (x integer, y integer);
CREATE TYPE
postgres=# create table foo(val abc[]);
CREATE TABLE
postgres=# insert into foo values (array[row(1,2)::abc]);
INSERT 0 1
postgres=# insert into foo values (array[row('1','2')::abc]);
INSERT 0 1
postgres=# select * from foo ;
val
---
{"(1,2)"}
{"(1,2)"}
(2 rows)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Populating array of composite datatype
> On Wed, Aug 7, 2013 at 3:53 AM, Raghavendra < > raghavendra@enterprisedb.com> wrote: > >> >> postgres=# insert into foo values (array[row(1,2)::abc]); >> >> > Also because all array members must be of the same db type, you can: > > insert into foo values (array[row(1,2)]::abc[]). This can be helpful if > you have more than one array member. > Agreed. Thank you Chris. --Raghav
Re: [GENERAL] PostgreSQL 9.2 Logging
> > My issue is the logging information is fairly missing / light. I only > see auth failures and nothing more. I tried to perform my 1st VACUUM > command on my database and I was hoping to see something in the logs > showing it PASSED / FAILED or even was manually initiated by a > superuser role but I see nothing... > > AFAIK, there's no separate flags written in logs for any utility commands like succeeded or not. Albeit, you can log the command executed and the time consumed. > I don't need to log every single verbose thing this database server > does but I would expect something like a VACUUM command would be > warranted to be logged, no? > Do you PG veterans have any log change recommendations / changes I can > make which would help me understand what my system is doing? I don't > want to log every little thing possible and choke my disk with > informative logging but just basic 'what's going on' logs would be > awesome. > > You can control the logging model with many parameters in postgresql.conf file, however, log_min_duration_statement will help you log only those statements which are taking some X seconds. For example: If I want to log only statement which are taking more than a second, I might not bother what are those statement then I would set like: postgres=# show log_min_duration_statement ; log_min_duration_statement 1s (1 row) This will log every statement equal or greater than the values passed to it. Now in my logs: 2013-08-13 03:07:01.146 IST [14152]: [9-1] db=,user= LOG: parameter "log_min_duration_statement" changed to "1s" 2013-08-13 03:08:03.297 IST [26559]: [1-1] db=d1,user=postgres LOG: duration: 2159.281 ms statement: vacuum VERBOSE ANALYZE ; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] How to find out unused indexes?
On Tue, Sep 3, 2013 at 11:36 AM, ascot.m...@gmail.com wrote: > Hi, > > Can you please advise how to find out all unused indexes in PG? > > regards > Below wiki link should help; under "Unused Index" section: http://wiki.postgresql.org/wiki/Index_Maintenance --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.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] pg_largeobjects
On Wed, Sep 11, 2013 at 10:19 AM, James Sewell wrote: > Hello all, > > I have a table which makes use of pg_largeobjects. I am inserting rows > into the table as user1. If I connect to the database as user2 I can SELECT > data, but can not open the large object for reading (user1 can do this). I > don't want to set lo_compat_privileges as then user3 (who can't SELECT > from the services tables) would be able to read the large object. > > GRANT SELECT,UPATE ON LARGE OBJECT to user2; Will this work... --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Theory question
On Tue, Nov 12, 2013 at 6:57 AM, Jayadevan M wrote: > Hi, > What are the real differences between the bgwriter and checkpointer > process? Both of them write data from the buffer to the data files, right? > Is it just a matter of 'when' they write? > Regards, > Jayadevan > Expect some corrections by others on my understanding described below. AFAIK, they share the load of writing dirty-buffers to disk, though they are defined to serve different purpose. Basically, background writer process sole function is to write "dirty" shared buffers to disk and evict those pages from shared buffer pool. Whereas checkpoint, arrives to write all dirty data pages in shared_buffers to disk only when checkpoint_timeout or when all checkpoint_segments are filled, whichever comes first. However, BG Writer (Writer Process) will be continuously trickle out dirty pages to disk so that by the time checkpoint arrives there will be left only with f ew dirty pages, instead of having lots of dirty pages to carry out by i tself alone and cause I/O loaded . --- Regards, Raghavendra
Re: [GENERAL] How to rename the same column name in dozens of tables in Postgres?
On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer wrote: > Hi there, > > I have dozens of tables where I need to rename one column in each of > these. Is there any generic way to do that? > I am not really familiar with the scripting possibilities of Postgres. > > Thanks a lot for your help! > > Stefan > > You can do with script. I made one on fly for this, other's might have more polished version. -bash-4.1$ more rename.sh #!/bin/bash OLDCOLUMN=aa NEWCOLUMN=a for i in $(psql -t -c "select table_schema||'.'||table_name from information_schema.tables where table_schema='public';") do /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i rename column $OLDCOLUMN to $NEWCOLUMN;" done Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your requirement. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] How to rename the same column name in dozens of tables in Postgres?
On Tue, Dec 17, 2013 at 2:23 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer < > stefan.schwar...@unep.org> wrote: > >> Hi there, >> >> I have dozens of tables where I need to rename one column in each of >> these. Is there any generic way to do that? >> > I am not really familiar with the scripting possibilities of Postgres. >> >> Thanks a lot for your help! >> >> Stefan >> >> > You can do with script. I made one on fly for this, other's might have > more polished version. > > -bash-4.1$ more rename.sh > #!/bin/bash > OLDCOLUMN=aa > NEWCOLUMN=a > for i in $(psql -t -c "select table_schema||'.'||table_name from > information_schema.tables where table_schema='public';") > do > /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i > rename column $OLDCOLUMN to $NEWCOLUMN;" > done > > Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your > requirement. > > One more way from command line -bash-4.1$ OLDCOLUMN=xyz -bash-4.1$ NEWCOLUMN=abc -bash-4.1$ psql -c "select 'alter table '||table_schema||'.'||table_name||' rename column $OLDCOLUMN to $NEWCOLUMN ;' from information_schema.tables where table_schema='public';" | psql --Raghav
Re: [GENERAL] what does the error mean?
On Tue, Dec 31, 2013 at 12:20 AM, AI Rumman wrote: > Hi, > > What does the error message mean? > > ERROR: unexpected chunk number 1 (expected 2) for toast value 179638221 > in pg_toast_16700 > > Please let me know. > > Thanks. > Well explained here... http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html --Raghav
Re: [GENERAL] general questions
On Thu, Jan 9, 2014 at 5:04 AM, Tom Lane wrote: > CS DBA writes: > > 1) \d and schema's > > - I setup 2 schema's (sch_a and sch_b) > > - I added both schema's to my search_path > > - I created 2 tables: sch_a.test_tab and sch_b.test_tab > > > If I do a \d with no parameters I only see the first test_tab table > > based on the order of my search_path. > > I get that any queries will use the first found table if I don't specify > > the schemaname but > > if I'm looking for a full list (i.e. \d with no parameters) I would > > think I should get a full list back > > > Is this intentional? > > Yes. If you want to see stuff that's invisible in your current search > path, use "\d *.*". That's even documented somewhere ... > > As Tom already said, am adding document pointer, you can find i n "patterns" . http://www.postgresql.org/docs/9.3/static/app-psql.html > > 3) Can I force unaligned mode AND no wrap for psql output? > > For both unaligned AND no wrap, I guess you need to take help of PAGER and " psql -A " or " p ostgres=# \a " or "postgres=# \pset format unaligned " I would try like: export PAGER='less -RSX'// It no wraps the output psql -A // Unaligned -- Raghav EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] PostgreSQL 9.2.4 using large amount of memory
Thanks On 27 Jan 2014 22:35, "Bhushan Pathak" wrote: > > Hello, > > We have recently shifted to postgresql version 9.2.4 from 9.1.3. After the migration, we observed that some of our delete queries on single table [which have triggers, which in turn call other functions] have started consuming large amounts of memory. > > In 9.1.3, this usage was upto 25MB with the same load on the same server. With 9.2.4 it has jumped upto ~580 MB. We are monitoring the RES column from top output to get the memory usage. > > Our migration method from 9.1.3 to 9.2.4 was take a dump, un-install 9.1.3, install 9.2.4 & restore the dump. > > I also went through the thread - > http://postgresql.1045698.n5.nabble.com/Memory-usage-after-upgrade-to-9-2-4-td5752733.html > > In the thread in the end it is mentioned that there was some data corruption & points to 9.1.6 release notes. I went through the release notes & only thing of note that I found was the re-indexing or performing vacuum operation in case of in-place upgrade, which is not the case for me. > > Any help/pointers in debugging would be helpful. > > Thanks > Bhushan > Just wanted to know, after upgrade as a part of process have you performed ANALYZE on the database. I agree this might not relate to the question but am curious to know this issue raised after proper upgrade method. --Raghav
Re: [GENERAL] stand by is starting until I do some work in the primary
On Sun, Feb 23, 2014 at 12:18 PM, Jov wrote: > Today I reinstall a pg extension,then restart the primary and slave. the > primary is OK and I can login,and the pg_stat_replication view show the > slave is connected. > but when I try to login the slave,it shows: > FATAL:the database system is starting up > > I wait several minutes,but it still show the same error. and the primary > is no activities,the xlog dir in slave have only 2 WAL files,WAL receiver > is there and seams do nothing.I try to restart the slave several times,but > nothing help.I even try to use pg_xlogdump to analyze the 2 WAL files,but > find nothing abnormal. > > But,when I login to the primary,run: > create table xx as select * from big_table. > > It produce many WAL，and then the slave became normal and I can login. > > it is instresting.I always think that when the pg_stat_replication show > the slave there,it means the slave is OK and can be send read stmt.We have > some monitor use this and some udf get the alived slave for client read.Now > I find I'm wrong. > > Any comment？ > > Hot_standby parameter should be turned on at slave postresql.conf, is that checked ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > > Jov > blog: http:amutu.com/blog <http://amutu.com/blog> >
[GENERAL] Is it typo in pg_stat_replication column name in PG 9.4 ?
Hi, PostgreSQL 9.4 document for pg_stat_replication view mentions column name as "backend_xid", whereas when a view described it shows column name as "backend_xmin". http://www.postgresql.org/docs/devel/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW postgres=# select version(); -[ RECORD 1 ] version | PostgreSQL 9.4beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit postgres=# \d pg_stat_replication View "pg_catalog.pg_stat_replication" Column | Type | Modifiers --+--+--- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | * backend_xmin* | xid | state| text | sent_location| pg_lsn | write_location | pg_lsn | flush_location | pg_lsn | replay_location | pg_lsn | sync_priority| integer | sync_state | text | Is it a typo in pg_stat_replication view column ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] pg_reorg
> > > I've seen it, but catalog hacks always make me nervous. Anybody else have > good / bad experience to share? > > --scott > > It is observed, double the space required for this utility. Eg:- If the database is 4 gig, there should be 8gigs space. Best Regards, Raghavendra EnterpriseDB Corporation > > > >> merlin >> >> -- >> 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] Partitioning an existing table
On Mon, Apr 25, 2011 at 7:40 PM, Vick Khera wrote: > On Mon, Apr 25, 2011 at 6:46 AM, Phoenix Kiula wrote: > >> I could create a new parent table with child tables, and then INSERT >> all these millions of rows to put them into the right partition. But >> is that recommended? >> > > I did this twice (several years ago). Basically, you create your > partitions and set up the necessary triggers you want (I re-write the app to > insert directly into the correct partition). Then all new data starts going > into the partitions. Next, write a program that loops over the current > master table, and moves the data into each partition some small hunk at a > time, in a transaction. This can take a long time. For us, it took about 7 > days to move O(100m) rows. Then, when you're done, truncate the master > table, and enforce that no new data is allowed to be inserted into it. > > If you can, of course, try this out on a spare copy of that table. > > COPY command would be the best and fast way to do bulk inserts. Even for partitioning table from the base table, 1. Send all the data to .csv file with COPY TO command of the base table(which will undergo for partition). 2. Create the partition setup with TRIGGER's 3. Use COPY FROM command for inserting data into partition table. This approach will fasten the inserts. Increasing the memory will also help. Note: RULE based approach wont work with COPY command. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company Email: raghavendra@enterprisedb.com Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Partitioning an existing table
> > so now when your running application goes to query the table, it gets > doubles? if you do it in transactions, then how long are you going to cause > the master table to be locked when doing such a bulk delete? > > my point is to minimize service interruption, and that means moving small > hunks at a time to minimize the locks needed. > > Agreed, if you are pointing to the application.. The partitioning documentation in PG is very clear on how to partition > a new table. Create child tables, and have triggers that manage > INSERT, UPDATE and DELETE commands. > How about doing this with existing massive tables? (Over 120 million rows) > I could create a new parent table with child tables, and then INSERT > all these millions of rows to put them into the right partition. But > is that recommended? Here, I would go with COPY command rather than INSERT. Firstly, setup the partition/child tables with relevant triggers and calling function on it. Use COPY FROM command pointing to parent table by calling the .csv file(created on MASSIVE table). Triggers will push the data to the respective child tables. Faster and efficient way. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company Email: raghavendra@enterprisedb.com Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Re: Create Database automatacally after silent installation of postgresql. ?
On Wed, Apr 27, 2011 at 6:38 PM, Thomas Kellerer wrote: > hirenlad, 27.04.2011 09:47: > > Hiii >> Hey i m using postgresql 8.4. now i m install postgresql8.4 silently >> and it work properly, no issue during this process. Now problem is i want >> to >> create one database automatically after install postgresql 8.4. >> >> Can u plz inform me is it possible ? and if it is possible then how ? >> >> > Simply run initdb after your installation is finished. > > If you say, new database in the same cluster(/data directory running on 5432 port) then by default there will be three databases(postgres,template0,template1), for new you can use CREATE DATABASE command. If you want another cluster, you can use INITDB as said by Thomas Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company Email: raghavendra@enterprisedb.com Blog: http://raghavt.blogspot.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] load sql from the file in postgresql
> > \i C:\psql\loaddata.sql > > > But it says " C:: Permission denied" > > Give the permission to postgres user on 'loaddata.sql' file. Right click and in properties give full access to postgres user. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company Email: raghavendra@enterprisedb.com Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Partitioning an existing table
> > Thanks everyone for the excellent suggestions. >> >> Vick/Greg, thanks in particular for this reference. The doc gives me >> ideas for other things too! >> > > +1 > I've been trying to get the OSCON folk to accept this talk for several > years now, to reach a wider audience. Seems they don't like me... :( > Seems they missed one valid presentation :) Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Bidirectional replication
Best to start with.. http://bucardo.org/wiki/Bucardo/Installation Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, May 3, 2011 at 5:34 PM, tushar nehete wrote: > Thanks you all, > I started with Bucardo. I installed activeperl 5.12 on my Linux(RHEL5.5) > server. > Can you please suggest some link which describe the installation steps in > details. > > > Thanks, > Tushar > > On Tue, May 3, 2011 at 2:49 PM, Simon Riggs wrote: > >> On Tue, May 3, 2011 at 7:31 AM, Sim Zacks wrote: >> >> > I have heard good things about Bucardo, though I haven't tried it myself >> > yet. I was warned that it would be risky to have 2 masters that have the >> > same tables modified in both because of issues such as delayed sync, >> race >> > conditions and other such goodies that may corrupt the meaning of the >> data. >> >> >> Just to be clear and fair to Bucardo, I would add a few points. >> >> All multi-master replication solutions that use an optimistic >> mechanism require "conflict resolution" cases and code. This is the >> same with SQLServer and Oracle etc.. Referring to a well known problem >> as a race condition seems to introduce doubt and fear into a situation >> that is well understood. Bucardo does offer hooks for conflict >> resolution to allow you to program around the issues. >> >> So if I felt that multi-master replication was the right way to go for >> a solution, Bucardo is a good choice. >> >> Just to add other info: if multi-master replication uses pessimistic >> coherence, then the coherence mechanism can also be a source of >> contention and/or cause the need for alternative kinds of conflict >> resolution. >> >> -- >> Simon Riggs http://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] Bidirectional replication
One more point, Please take into consideration the points mentioned by Simon Riggs in your testing. Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, May 3, 2011 at 5:41 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > Best to start with.. > > http://bucardo.org/wiki/Bucardo/Installation > > Best Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > > > On Tue, May 3, 2011 at 5:34 PM, tushar nehete wrote: > >> Thanks you all, >> I started with Bucardo. I installed activeperl 5.12 on my Linux(RHEL5.5) >> server. >> Can you please suggest some link which describe the installation steps in >> details. >> >> >> Thanks, >> Tushar >> >> On Tue, May 3, 2011 at 2:49 PM, Simon Riggs wrote: >> >>> On Tue, May 3, 2011 at 7:31 AM, Sim Zacks wrote: >>> >>> > I have heard good things about Bucardo, though I haven't tried it >>> myself >>> > yet. I was warned that it would be risky to have 2 masters that have >>> the >>> > same tables modified in both because of issues such as delayed sync, >>> race >>> > conditions and other such goodies that may corrupt the meaning of the >>> data. >>> >>> >>> Just to be clear and fair to Bucardo, I would add a few points. >>> >>> All multi-master replication solutions that use an optimistic >>> mechanism require "conflict resolution" cases and code. This is the >>> same with SQLServer and Oracle etc.. Referring to a well known problem >>> as a race condition seems to introduce doubt and fear into a situation >>> that is well understood. Bucardo does offer hooks for conflict >>> resolution to allow you to program around the issues. >>> >>> So if I felt that multi-master replication was the right way to go for >>> a solution, Bucardo is a good choice. >>> >>> Just to add other info: if multi-master replication uses pessimistic >>> coherence, then the coherence mechanism can also be a source of >>> contention and/or cause the need for alternative kinds of conflict >>> resolution. >>> >>> -- >>> Simon Riggs http://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] [ADMIN] Can we Flush the Postgres Shared Memory ?
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. > This command will empty the PSM... Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Unexpected exit of Postgres terminal
Hi Adarsh, This isssue has fixed in PPSS latest version. However here is workaround for it. Step 1: Install Libedit module from Yum Repository. [root@239435-db9 ~]# yum install libedit.x86_64 [root@239435-db9 ~]# rpm -q libedit.x86_64 libedit-2.11-2.20080712cvs.el5 Step 2: Identify the location of installed libedit binary location. [root@239435-db9 ~]# rpm -q libedit.x86_64 --filesbypkg libedit /usr/lib64/libedit.so.0 libedit /usr/lib64/libedit.so.0.0.27 libedit /usr/share/doc/libedit-2.11 libedit /usr/share/doc/libedit-2.11/COPYING libedit /usr/share/doc/libedit-2.11/ChangeLog libedit /usr/share/doc/libedit-2.11/THANKS [root@239435-db9 ~]# pwd /root Step 3: Take the backup of bundled libedit binary [root@239435-db9 ~]# cd /opt/PostgreSQL/9.0/lib/ [root@239435-db9 lib]# ls -l libedit* -rwxr-xr-- 1 root daemon 192738 Dec 14 04:46 libedit.so [root@239435-db9 lib]# mv libedit.so libedit.so.bk Step 4: Soft link the newly installed libedit binary to PostgreSQL installed location [root@239435-db9 lib]# ln -s /usr/lib64/libedit.so.0 /opt/PostgreSQL/9.0/lib/libedit.so Now try connecting to Server with psql... --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Thu, May 19, 2011 at 4:39 PM, Adarsh Sharma wrote: > Dear all, > > Today I need to use crosstab function but Postgresql says that there is no > function crosstab, so i try to install it by : > > bin/psql -Upostgres test -f > '/opt/PostgresPlus/9.0SS/share/postgresql/contrib/tablefunc.sql' > & it creates the functions. > > But know when I issue any query in Postgres , don't know why this appears. > > test=# select * from ct; > id | rowid | attribute | value > +---+---+--- > 1 | test1 | att1 | val1 > 2 | test1 | att2 | val2 > 3 | test1 | att3 | val3 > 4 | test1 | att4 | val4 > 5 | test2 | att1 | val5 > 6 | test2 | att2 | val6 > 7 | test2 | att3 | val7 > 8 | test2 | att4 | val8 > (8 rows) > > *** glibc detected *** bin/psql: free(): invalid next size (fast): > 0x0b684f40 *** > === Backtrace: = > /lib64/libc.so.6[0x34e9a722ef] > /lib64/libc.so.6(cfree+0x4b)[0x34e9a7273b] > bin/psql[0x4139b4] > bin/psql[0x414e44] > bin/psql[0x415a68] > bin/psql[0x408d82] > bin/psql[0x40d9d6] > bin/psql[0x40fa7e] > /lib64/libc.so.6(__libc_start_main+0xf4)[0x34e9a1d994] > bin/psql[0x403a69] > === Memory map: > 0040-00456000 r-xp 08:01 18267785 > /opt/PostgresPlus/9.0SS/bin/psql > 00655000-00656000 rw-p 00055000 08:01 18267785 > /opt/PostgresPlus/9.0SS/bin/psql > 00656000-00657000 rw-p 00656000 00:00 0 > 0b66d000-0b68e000 rw-p 0b66d000 00:00 0 > [heap] > 3485e0-3485e03000 r-xp 08:01 18269363 > /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 > 3485e03000-3486002000 ---p 3000 08:01 18269363 > /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 > 3486002000-3486003000 rw-p 2000 08:01 18269363 > /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 > 34e8a0-34e8a1c000 r-xp 08:01 13291123 > /lib64/ld-2.5.so > 34e8c1b000-34e8c1c000 r--p 0001b000 08:01 13291123 > /lib64/ld-2.5.so > 34e8c1c000-34e8c1d000 rw-p 0001c000 08:01 13291123 > /lib64/ld-2.5.so > 34e9a0-34e9b4d000 r-xp 08:01 13291124 > /lib64/libc-2.5.so > 34e9b4d000-34e9d4d000 ---p 0014d000 08:01 13291124 > /lib64/libc-2.5.so > 34e9d4d000-34e9d51000 r--p 0014d000 08:01 13291124 > /lib64/libc-2.5.so > 34e9d51000-34e9d52000 rw-p 00151000 08:01 13291124 > /lib64/libc-2.5.so > 34e9d52000-34e9d57000 rw-p 34e9d52000 00:00 0 > 34e9e0-34e9e82000 r-xp 08:01 13291125 > /lib64/libm-2.5.so > 34e9e82000-34ea081000 ---p 00082000 08:01 13291125 > /lib64/libm-2.5.so > 34ea081000-34ea082000 r--p 00081000 08:01 13291125 > /lib64/libm-2.5.so > 34ea082000-34ea083000 rw-p 00082000 08:01 13291125 > /lib64/libm-2.5.so > 34ea20-34ea202000 r-xp 08:01 13291130 > /lib64/libdl-2.5.so > 34ea202000-34ea402000 ---p 2000 08:01 13291130 > /lib64/libdl-2.5.so > 34ea402000-34ea403000 r--p 2000 08:01 13291130 > /lib64/libdl-2.5.so > 34ea403000-34ea404000 rw-p 3000 08:01 13291130 > /lib64/libdl-2.5.so > 34ea60-34ea616000 r-xp 08:01 13291126 > /lib64/libpthread-2.5.so > 34ea616000-34ea815000 ---p 00016000 08:01 13291126 > /lib64/libpthread-2.5.so > 34ea815000-34ea816000 r--p 00015000 08:01 13291126 > /lib64/libpthread-2.5.so > 34ea816000-34ea817000 rw-p 00016000 08:01 13291126 > /lib64/libpthread-2.5.so > 34ea817000-34ea81b000 rw-p 34ea817000 00:00 0 > 34eae0-34eae14000 r-xp 08:01 20286121 > /usr/lib64
Re: [GENERAL] Unexpected exit of Postgres terminal
Small change replace all PG path with PPSS paths... --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Thu, May 19, 2011 at 4:46 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > Hi Adarsh, > > This isssue has fixed in PPSS latest version. However here is workaround > for it. > > Step 1: Install Libedit module from Yum Repository. > > [root@239435-db9 ~]# yum install libedit.x86_64 > > [root@239435-db9 ~]# rpm -q libedit.x86_64 > libedit-2.11-2.20080712cvs.el5 > > Step 2: Identify the location of installed libedit binary location. > > [root@239435-db9 ~]# rpm -q libedit.x86_64 --filesbypkg > libedit /usr/lib64/libedit.so.0 > libedit /usr/lib64/libedit.so.0.0.27 > libedit /usr/share/doc/libedit-2.11 > libedit /usr/share/doc/libedit-2.11/COPYING > libedit /usr/share/doc/libedit-2.11/ChangeLog > libedit /usr/share/doc/libedit-2.11/THANKS > > [root@239435-db9 ~]# pwd > /root > > Step 3: Take the backup of bundled libedit binary > > [root@239435-db9 ~]# cd /opt/PostgreSQL/9.0/lib/ > > > [root@239435-db9 lib]# ls -l libedit* > -rwxr-xr-- 1 root daemon 192738 Dec 14 04:46 libedit.so > > [root@239435-db9 lib]# mv libedit.so libedit.so.bk > > Step 4: Soft link the newly installed libedit binary to PostgreSQL > installed location > > [root@239435-db9 lib]# ln -s /usr/lib64/libedit.so.0 > /opt/PostgreSQL/9.0/lib/libedit.so > > Now try connecting to Server with psql... > > --- > Best Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > > > On Thu, May 19, 2011 at 4:39 PM, Adarsh Sharma > wrote: > >> Dear all, >> >> Today I need to use crosstab function but Postgresql says that there is no >> function crosstab, so i try to install it by : >> >> bin/psql -Upostgres test -f >> '/opt/PostgresPlus/9.0SS/share/postgresql/contrib/tablefunc.sql' >> & it creates the functions. >> >> But know when I issue any query in Postgres , don't know why this appears. >> >> test=# select * from ct; >> id | rowid | attribute | value >> +---+---+--- >> 1 | test1 | att1 | val1 >> 2 | test1 | att2 | val2 >> 3 | test1 | att3 | val3 >> 4 | test1 | att4 | val4 >> 5 | test2 | att1 | val5 >> 6 | test2 | att2 | val6 >> 7 | test2 | att3 | val7 >> 8 | test2 | att4 | val8 >> (8 rows) >> >> *** glibc detected *** bin/psql: free(): invalid next size (fast): >> 0x0b684f40 *** >> === Backtrace: = >> /lib64/libc.so.6[0x34e9a722ef] >> /lib64/libc.so.6(cfree+0x4b)[0x34e9a7273b] >> bin/psql[0x4139b4] >> bin/psql[0x414e44] >> bin/psql[0x415a68] >> bin/psql[0x408d82] >> bin/psql[0x40d9d6] >> bin/psql[0x40fa7e] >> /lib64/libc.so.6(__libc_start_main+0xf4)[0x34e9a1d994] >> bin/psql[0x403a69] >> === Memory map: >> 0040-00456000 r-xp 08:01 18267785 >> /opt/PostgresPlus/9.0SS/bin/psql >> 00655000-00656000 rw-p 00055000 08:01 18267785 >> /opt/PostgresPlus/9.0SS/bin/psql >> 00656000-00657000 rw-p 00656000 00:00 0 >> 0b66d000-0b68e000 rw-p 0b66d000 00:00 0 >> [heap] >> 3485e0-3485e03000 r-xp 08:01 18269363 >> /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 >> 3485e03000-3486002000 ---p 3000 08:01 18269363 >> /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 >> 3486002000-3486003000 rw-p 2000 08:01 18269363 >> /opt/PostgresPlus/9.0SS/lib/libtermcap.so.2 >> 34e8a0-34e8a1c000 r-xp 08:01 13291123 >> /lib64/ld-2.5.so >> 34e8c1b000-34e8c1c000 r--p 0001b000 08:01 13291123 >> /lib64/ld-2.5.so >> 34e8c1c000-34e8c1d000 rw-p 0001c000 08:01 13291123 >> /lib64/ld-2.5.so >> 34e9a0-34e9b4d000 r-xp 08:01 13291124 >> /lib64/libc-2.5.so >> 34e9b4d000-34e9d4d000 ---p 0014d000 08:01 13291124 >> /lib64/libc-2.5.so >> 34e9d4d000-34e9d51000 r--p 0014d000 08:01 13291124 >> /lib64/libc-2.5.so >> 34e9d51000-34e9d52000 rw-p 00151000 08:01 13291124 >> /lib64/libc-2.5.so >> 34e9d52000-34e9d57000 rw-p 34e9d52000 00:00 0 >> 34e9e0-34e9e82000 r-xp 08:01 13291125 >> /lib64/libm-2.5.so >> 34e9e82000-34ea081000 ---p 00082000 08:01 13291125 >> /lib64/libm-2.5.so >> 34ea081000-34ea082000 r--p 00081000 08:01 13291125 >> /lib64/libm-2.5.so >> 34ea082000-34ea083000 rw-p 00082000 08:01 13291125 >> /lib64/libm-2.5.so >
Re: [GENERAL] how to find a tablespace for the table?
That's right, if the tables are in default tablespace, those columns will be
blank, if any of the table created under any of the
tablespace then it will show up.
Eg:-
postgres=# show default_tablespace ; (this would be blank becz am in
pg_default/pg_global)
default_tablespace
(1 row)
postgres=# create table foo(id int);
CREATE TABLE
postgres=# select * from pg_tables where tablename='foo';
-[ RECORD 1 ]-
schemaname | public
tablename | foo
tableowner | postgres
tablespace |
hasindexes | f
hasrules| f
hastriggers | f
Now I have the table in one of my tablespace.
postgres=#create table tab_test(id int) tablespace t1;
Expanded display is on.
postgres=# select * from pg_tables where tablename='tab_test';
-[ RECORD 1 ]-
schemaname | public
tablename | tab_test
tableowner | postgres
*tablespace | t1*
hasindexes | f
hasrules| f
hastriggers | f
If you want to know the tablespace default information, you can try with
this query.
select spcname, case spcname when 'pg_default' then (select setting from
pg_settings where name = 'data_directory')||'/base' when 'pg_global' then
(select setting from pg_settings where name = 'data_directory')||'/global'
else spclocation end from pg_tablespace;
To get the exact table's and its tablespace's below query will work.
select relname,reltablespace from pg_class where reltablespace in(select
oid from pg_tablespace where spcname not in ('pg_default','pg_global'));
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Mon, Jun 20, 2011 at 11:40 PM, hyelluas wrote:
> thank you Greg,
>
> here is what I get, I createed view as you suggested.
> I'm not sure why tablespace column is empty
>
> profiler1=# select * from pg_tables where schemaname ='public' limit 10;
> schemaname |tablename| tableowner | tablespace |
> hasindexes
> | hasrules | hastri
> ers
>
> +-++++--+---
>
> public | ttt | postgres |
> | f | f| f
> public | summ_hrly_1514609 | postgres | | t
> | f| f
> public | summ_5min_1514610 | postgres | | t
> | f| f
> public | exp_cnt | postgres |
> | f | f| f
> public | auth_type| postgres |
> |
> t | f| f
> public | druid_mapping | postgres | |
> t | f| f
> public | application_category | postgres | | t
> | f| f
> public | application_risk | postgres |
> | t | f| f
> public | policy_history| postgres |
> | t | f| f
> public | datasource | postgres |
> |
> t | f| f
> (10 rows)
>
>
> thank you.
> Helen
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.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] how to find a tablespace for the table?
let me correct on this query, it shows only those tables which wont belong
to default_tablespace...
select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, Jun 21, 2011 at 12:55 AM, Raghavendra <
raghavendra@enterprisedb.com> wrote:
> hmmmStrange..
>
> What is the output of
>
> select oid,* from pg_tablespace;
>
> and
>
> select relname,reltablespace from pg_class where relname='
> application_category';
>
>
> ---
> Best Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
> On Tue, Jun 21, 2011 at 12:48 AM, hyelluas wrote:
>
>> well,
>>
>> here is the query :
>>
>> profiler1=# select relname,reltablespace from pg_class where reltablespace
>> in(select oid from pg_tablespace where spcname not in
>> ('pg_default','pg_global'));
>> relname | reltablespace
>> -+---
>>
>>
>> why it shows no records?
>>
>> profiler1=# select * from pg_catalog.pg_tables where
>> tablename='application_category';
>> schemaname | tablename | tableowner | tablespace | hasindexes
>> |
>> hasrules | hastrigge
>> rs
>>
>> +--++++--+--
>> ---
>> public | application_category | postgres || t
>> |
>> f| f
>> (1 row)
>>
>>
>> and that query show empty for the tablespace...
>>
>> thank you
>> Helen
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.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] how to find a tablespace for the table?
hmmmStrange..
What is the output of
select oid,* from pg_tablespace;
and
select relname,reltablespace from pg_class where relname='
application_category';
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, Jun 21, 2011 at 12:48 AM, hyelluas wrote:
> well,
>
> here is the query :
>
> profiler1=# select relname,reltablespace from pg_class where reltablespace
> in(select oid from pg_tablespace where spcname not in
> ('pg_default','pg_global'));
> relname | reltablespace
> -+---
>
>
> why it shows no records?
>
> profiler1=# select * from pg_catalog.pg_tables where
> tablename='application_category';
> schemaname | tablename | tableowner | tablespace | hasindexes |
> hasrules | hastrigge
> rs
>
> +--++++--+--
> ---
> public | application_category | postgres || t |
> f| f
> (1 row)
>
>
> and that query show empty for the tablespace...
>
> thank you
> Helen
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.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] how to find a tablespace for the table?
> > profiler1=# select * from pg_catalog.pg_tables where > tablename='application_category'; > schemaname | tablename | tableowner | tablespace | hasindexes | > hasrules | hastrigge > rs > > +--++++--+-- > --- > public | application_category | postgres || t | > f| f > (1 row) Whats the output of this.. select relname,reltablespace from pg_class where relname=' application_category'; --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Jun 21, 2011 at 2:16 AM, hyelluas wrote: > here it is : > > > profiler1=# select oid,* from pg_tablespace; > oid | spcname | spcowner | spclocation | spcacl > ---++--+--+ > 1663 | pg_default | 10 | | > 1664 | pg_global | 10 | | > 19955 | profiler1 | 10 | /data/psql/profiler1 | > (3 rows) > > > profiler1=# select relname,reltablespace from pg_class where > reltablespace=19955; > relname | reltablespace > -+--- > (0 rows) > > > thanks > Helen > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508020.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] how to find a tablespace for the table?
Can you give a try updating the catalogs with ANALYZE command and re-check ? --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Jun 21, 2011 at 2:22 AM, hyelluas wrote: > profiler1=# select relname,reltablespace from pg_class where > relname='application_category'; > relname| reltablespace > --+--- > application_category | 0 > (1 row) > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.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] how to find a tablespace for the table?
Actually to say, everything looks ok to me, until unless you say the table * '**application_category'* on other tablespace ... :) profiler1=# select relname,reltablespace from pg_class where >> relname='application_category'; >> relname| reltablespace >> --+--- >> application_category | 0 >> (1 row) >> >> Above result, indicates that its in default tablespace. http://www.postgresql.org/docs/9.0/static/catalog-pg-class.html Regards Raghav > >> -- >> View this message in context: >> http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.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] Two indexes on same column
On Wed, Jul 20, 2011 at 2:39 PM, Vlastimil Krejcir wrote: > Hi, > > what index is used (and according to what rules) when there are two (or > more) different indexes defined on one column? Assume: > > CREATE TABLE example ( > id SERIAL PRIMARY KEY, > ...); > CREATE INDEX example_id_idx ON example USING hash (id); > > By default there are btree index created and the hash index is then > created. So there are two indexes on column "id". Are there described > somewhere what index is used and when? Does it depend on query analyzer and > planner? > > Thanks > > You are right, depends on the optimizer and query to which index to choose. EXPLAIN command on the query will give you the optimizer path. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] repmgr and archive_mode
On Fri, Jul 29, 2011 at 1:42 PM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > Hi, > In the repmgr README, it suggests configuration should include: > > archive_mode = on > archive_command = 'cd .' > > Could someone explain why we need archive_mode enabled? In my own testing > of Pg's streaming replication, I've found it to work without this enabled. > > AFAIK, enabling 'archive_mode' parameter turns the cluster into Archive-log Mode and generates a copy of WAL segements to ARCHIVE destination. Strange to know that Streaming replication done without this parameter, could you share the output of the below command in the PRIMARY cluster. show archive_mode show archive_command --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Copy command to handle view for my export requirement
Nope, you need to be in latest version as Andreas said. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Thu, Aug 11, 2011 at 4:51 PM, Siva Palanisamy wrote: > Hi Andreas, > > FYI, I am using PostgreSQL 8.1.4. > > Thanks and Regards, > Siva. > > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy > Sent: Thursday, August 11, 2011 4:48 PM > To: Andreas Kretschmer; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Copy command to handle view for my export > requirement > > Hi Andreas, > > I tried the command as below. It failed. Please correct me. > > \copy (select * from view1) to '/sample.csv' delimiters ',' csv header; > ERROR: \copy: parse error at "select" > > Thanks and Regards, > Siva. > > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer > Sent: Thursday, August 11, 2011 2:23 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Copy command to handle view for my export > requirement > > Siva Palanisamy wrote: > > > Hi All, > > > > > > > > I understand that copy and \copy commands in PostgreSQL work only for > tables. I > > want it to export the data from varies tables. Instead, I can create a > view for > > the list of tables. Can the copy or \copy commands be utilized to operate > on > > views directly? Please let me know on this. > > Sure, you can do that (with recent versions) with: > > copy (select * from your_view) to ... > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > ::DISCLAIMER:: > > --- > > The contents of this e-mail and any attachment(s) are confidential and > intended for the named recipient(s) only. > It shall not attach any liability on the originator or HCL or its > affiliates. Any views or opinions presented in > this email are solely those of the author and may not necessarily reflect > the opinions of HCL or its affiliates. > Any form of reproduction, dissemination, copying, disclosure, modification, > distribution and / or publication of > this message without the prior written consent of the author of this e-mail > is strictly prohibited. If you have > received this email in error please delete it and notify the sender > immediately. Before opening any mail and > attachments please check them for viruses and defect. > > > --- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- > 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] Copy command to handle view for my export requirement
> > COMMAND: copy (select * from employee) to 'C:/emp.csv' > ERROR: could not open file "C:/emp.csv" for writing: Permission denied > ** Error ** > ERROR: could not open file "C:/emp.csv" for writing: Permission denied > SQL state: 42501 > > COMMAND: \copy (select * from employee) to 'C:/emp.csv' > ERROR: syntax error at or near "\" > LINE 1: \copy (select * from employee) to 'C:/emp.csv' >^ > ** Error ** > ERROR: syntax error at or near "\" > SQL state: 42601 > > Please correct me where I am going wrong. FYI, I am running under the > administrator accounts of both Windows Login and PostgreSQL. > > Two things, 1. you need to have a proper permissions where the .csv file creating. 2. In windows you need to use as below postgres=#\copy (select * from employee) to 'C:\\emp.sql' Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Copy command to handle view for my export requirement
> > > You are not using psql. "\copy" is a psql command. I don't think it's > supported by PgAdmin III, though I could be wrong. > > Right, '\copy' is not supported in PgAdmin III. --Raghav
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 7:17 PM, Ray Stell wrote: > On Tue, Aug 23, 2011 at 06:23:58AM -0700, Adrian Klaver wrote: > > On Tuesday, August 23, 2011 4:14:15 am Ray Stell wrote: > > > On Tue, Aug 23, 2011 at 02:01:10AM -0700, alexondi wrote: > > > > Hi! > > > > Do I need setup wal archiving (archiving_mode = on) setup when I use > > > > streaming replication? > > > > > > yes > > > > http://www.postgresql.org/docs/current/interactive/high-availability.html > > > > Actually no. Streaming will work without archiving. For a quick > introduction > > see: > > > > > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication > > right, you don't need to. I find it to be a best practice, which is > different. > > Question: Is it a best practice to keep cluster in Archive_mode = on and setup streaming replication or just leave archive_mode=off? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 10:49 PM, Ray Stell wrote: > On Tue, Aug 23, 2011 at 08:30:55PM +0530, Raghavendra wrote: > > > > Is it a best practice to keep cluster in Archive_mode = on and setup > > streaming replication or just leave archive_mode=off? > > Depends. The reason for creating WAL is in case they are needed for > recovery. > In the event that the stby host goes down, if bringing it back into service > exceeds wal_keep_segments on the primary then you would need to rebuild > the standby. This is a local decision and should be made based on business > rules. > Thanks Ray. --Raghav
Re: [GENERAL] pgfoundry.org is not accessible
Now am able to access it. But for sometime I was not able to access it. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Fri, Sep 2, 2011 at 3:34 AM, Raghavendra < raghavendra@enterprisedb.com> wrote: > Hi All, > > Am unable to access pgfoundry.org site. Does anyone facing the same > problem ? > > Oops! Google Chrome could not connect to pgfoundry.org > > Try reloading: pgfoundry.org <http://pgfoundry.org/> > Additional suggestions: > >- Access a **cached > copy<http://www.google.com/search?q=cache:http://pgfoundry.org/> of >pgfoundry.org >- Search on Google: > > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > >
[GENERAL] pgfoundry.org is not accessible
Hi All, Am unable to access pgfoundry.org site. Does anyone facing the same problem ? Oops! Google Chrome could not connect to pgfoundry.org Try reloading: pgfoundry.org <http://pgfoundry.org/> Additional suggestions: - Access a **cached copy<http://www.google.com/search?q=cache:http://pgfoundry.org/> of pgfoundry.org - Search on Google: --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] pgfoundry.org is not accessible
Hopefully It should be back after sometime :) --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Sep 6, 2011 at 3:17 AM, Tomas Vondra wrote: > On 2 Září 2011, 7:36, Magnus Hagander wrote: > > Yeah, all hub.org hosted services had a rather long downtime again > > yesterday. They seem to be back up now. > > And down again :-( > > Tomas > >
[GENERAL] How to get Transaction Timestamp ?
Respected All, Can we get the transaction timestamp for INSERT/UPDATE/DELETE ran against table in the database ? postgres=# create table trx_test(id int, name char(30)); CREATE TABLE postgres=# insert into trx_test VALUES (1,'AAA'); INSERT 0 1 postgres=# insert into trx_test VALUES (2,'BBB'); INSERT 0 1 postgres=# insert into trx_test VALUES (3,'CCC'); INSERT 0 1 postgres=# select xmin,* from trx_test ; xmin | id | name -++ 1348711 | 1 | AAA 1348712 | 2 | BBB 1348713 | 3 | CCC (3 rows) We can get a Transaction ID, but not the transaction timestamp when it performed. Kindly advice me. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] How to get Transaction Timestamp ?
Thank you for your valuable inputs. Agreed, with the help of two workarounds we can pull the trx-timestamp one with additional-column/trigger and another with log_line_prefix from pg_log/logs. However, I was curious to know any thing stored at Page-Level(like XID) to help me in getting the transaction timestamp. --Raghav On Sat, Sep 17, 2011 at 7:41 AM, Marti Raudsepp wrote: > On Fri, Sep 16, 2011 at 21:39, Raghavendra > wrote: > > We can get a Transaction ID, but not the transaction timestamp when it > > performed. > > Short answer: You can't. Instead, add a new "timestamptz default > now()" column, that will get you the time of the insert. > > If you want the update time, create a BEFORE UPDATE ON x FOR EACH ROW > trigger on this table to update it. > > Regards, > Marti >
[GENERAL] In which case PG_VERSION file updates ?
Respected All, In which case $PGDATA/base/database-oid/PG_VERSION file updates ? I have observed, PG_VERSION file is created at DB creation time and will never get updated. I mean file PG_VERSION TIMESTAMP. Thanks in advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] In which case PG_VERSION file updates ?
Thanks Adrian Klaver, Provided link gives about the information of what PG_VERSION file, which am aware of:) In my observation, all the object related OID's, _fsm, _vm files under $PGDATA/base/database-oid/ directory will change as per the changes made to the database, whereas PG_VERSION file never changes. Because its the file tells on which version of Binary the Data directory is built upon. So, my question is, Is there any case, where PG_VERSION file updates with any of the utility process or PG_VERSION file never been touched by PG-Instance ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Sun, Sep 25, 2011 at 2:00 AM, Adrian Klaver wrote: > On Saturday, September 24, 2011 12:34:02 pm Raghavendra wrote: > > Respected All, > > > > In which case $PGDATA/base/database-oid/PG_VERSION file updates ? > > > > I have observed, PG_VERSION file is created at DB creation time and will > > never get > > updated. I mean file PG_VERSION TIMESTAMP. > > See here: > http://www.postgresql.org/docs/9.1/interactive/storage-file-layout.html > > > > > Thanks in advance. > > > > --- > > Regards, > > Raghavendra > > EnterpriseDB Corporation > > Blog: http://raghavt.blogspot.com/ > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] In which case PG_VERSION file updates ?
> > From what I can see, PG_VERSION is written at database creation to document > the > major version of the Postgres instance used to create the database. Since > it > only holds the major version string (i.e. 9.0) it is not touched during > minor > updates, for example 9.0.0 --> 9.0.1. Grepping the pg_upgrade code shows > it > might touch PG_VERSION. A quick grep on the rest of the source code shows > only > initdb writing out PG_VERSION, though it is checked by other code. This is > by no > means an in depth look and it would take some one with more knowledge of > Postgres internals to give you a definitive answer. Might be worth a post > on - > hackers. Thank you Adrian Klaver / Scott Marlowe for your valuable inputs. I got clarified. As said, PG_VERSION file is created at the time of Database creation. Sometimes, question arises that at what time database created. For this we don't have any information to get from pg_catalogs, so with PG_VERSION file timestamp we can pull database creation time. However, with your inputs its clear that when PG_VERSION file is touched. In pg_upgrade or any of the Postgres Internals touching PG_VERSION file will never get exact database creation time. I am not knowing why database creation time is not considered to be in pg_catalogs. -- Raghav
Re: [GENERAL] How can i get record by data block not by sql?
To know the page and row information : Select ctid,* from ; For more information at page-level, you can take the help of contrib module "pageinspect". Which is under pgsql-path/share/postgresql/contrib/pageinspect.sql --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ 2011/10/3 姜头 <104186...@qq.com> > How can i get record by data block not by sql? > > I want to read and write lots of data by data blocks, so i can form a > disk-resident tree by recording the block address. But i don't know how to > implement in postgresql. > Is there system function can do this? > Can someone help me?? Thank you very very much1 >
Re: [GENERAL] Restoring 2 Tables From All Databases Backup
Hi Adarsh, Filip workaround is right approach, since its plain text format you need to play with SED/AWK to pull those two tables. Following link will help you:- http://blog.endpoint.com/2010/04/restoring-individual-table-data-from.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ 2011/10/5 pasman pasmański > You should to create new database with two empty tables, set access > rights for all schemas readonly and pipe backup to this database. > > 2011/10/5, Dickson S. Guedes : > > 2011/10/5 Adarsh Sharma : > >> About 1 month ago, I take a complete databases backup of my Database > >> server > >> through pg_dumpall command. > >> Today I need to extract or restore only 2 tables in a database. > >> > >> Is it possible or I have to restore complete Databases again. Size of > >> backup > >> is 10 GB in .sql.gz format. > > > > If your dump was created using custom format [1] (pg_dump > > --format=custom or -Fc) you can do a pg_restore using --use-list and > > --list [2]. > > > > [1] > > > http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS > > [2] > > > http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS > > > > -- > > Dickson S. Guedes > > mail/xmpp: gue...@guedesoft.net - skype: guediz > > http://guedesoft.net - http://www.postgresql.org.br > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > > pasman > > -- > 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 on PostgreSQL
> > > Hi Guys, > > > > We are new to PostgreSQL world. > > > > But, our company is planning to migrate the one of the existing > > application to PostgreSQL from Oracle. > > 2.Is there any enterprise version available with all features? > > The free PostgreSQL comes with all available features; it's not a "lite" > version with paid "enterprise" add-ons. > > That said, there are separate companies that produce products based on > PostgreSQL. While many of these companies contribute to the PostgreSQL > core and are active members of the PostgreSQL community, they don't > control PostgreSQL. Any software they sell may be based on PostgreSQL, > but it's really a new and different product. These products add > additional functionality. One of the better-known is EnterpriseDB, who > have Pg variants with Oracle compatibility enhancements. > > As Craig said, there are very good tools out which will help you migrating from Oracle to PostgreSQL, am adding one from my end i.e., EnterpriseDB http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server Migration Studio which comes in separate bundle. http://www.enterprisedb.com/downloads/add-on-components-bundles --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Postgre Performance
Dear Yogesh, To get best answer's from community member's you need to provide complete information like,PG version, Server /Hardware info etc., So that it help's member's to assist you in right way. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Oct 18, 2011 at 7:27 PM, Deshpande, Yogesh Sadashiv (STSD-Openview) wrote: > Hello , > > ** ** > > We have a setup where in there are around 100 process running in parallel > every 5 minutes and each one of them opens a connection to database. We are > observing that for each connection , postgre also created on sub processes. > We have set max_connection to 100. So the number of sub process in the > system is close to 200 every 5 minutes. And because of this we are seeing > very high CPU usage. We need following information > > ** ** > > **1. **Is there any configuration we do that would pool the > connection request rather than coming out with connection limit exceed.*** > * > > **2. **Is there any configuration we do that would limit the sub > process to some value say 50 and any request for connection would get > queued. > > ** ** > > Basically we wanted to limit the number of processes so that client code > doesn’t have to retry for unavailability for connection or sub processes , > but postgre takes care of queuing? > > ** ** > > Thanks > > Yogesh >
Re: [GENERAL] Postgre Performance
> > > We need following information > > > > 1. Is there any configuration we do that would pool the connection > request rather than coming out with connection limit exceed. > > Use pgpool or pgbouncer. > > Use pgbouncer, which is a light weighted connection pooling tool, if you are not opting for load balancing. > > Basically we wanted to limit the number of processes so that client code > doesn't have to retry for unavailability for connection or sub processes , > but postgre takes care of queuing? > > For controlling unavailability of connections, it may be possible at application level but its not possible at Database level. However, if connections reaches max limit, DB will alert you as it reached max_connection. --Raghav
Re: [GENERAL] varchar for loops possible?
As Tom said, you need to declare tmp_var as per the result set coming from select distinct (value) column. I gave a try on it. create or replace function prn_test() returns void as $$ declare tmp_var test_table.name%type; ///Test_table with name column which is varchar(20) in my case begin for tmp_var in (select distinct name from test_table) loop raise notice 'Give anything here :) ... !!!'; update test_table set name=tmp_var; end loop; end; $$ language plpgsql; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Sat, May 19, 2012 at 2:10 AM, Tom Lane wrote: > "J.V." writes: > > for tmp_var in select distinct(value) from mytable where > > value2='literal' > > tmp_var has to be in ' ' ticks or will not work. it is failing on the > > first FOR statment stating: "invalid input syntax for integer: > > "some_distinct_value". > > Um, how do you have tmp_var declared? plpgsql seems to be acting > as though it's an integer variable, which is not what you need if > "value" is a varchar. > >regards, tom lane > > -- > 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] enhanced linestyles for psql
> > hello > I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and > border styles to console > http://postgres.cz/wiki/Pretty_borders_in_psql Thank you. And really a great patch to psql console lover's... :) http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] enhanced linestyles for psql
> > > > > http://postgres.cz/wiki/Enhanced-psql > > > > Is this also compatible to 9.1 ? > > > > no, this was prepared for 8.4. These features was just experiment and > only smaller subset is in core now. > > Oh ok. Thank you for the info. Multi-Headers, Macros of psql 8.4 were very interesting features, sustaining them in 9.1 would have matured psql console alot. --Raghav
Re: [GENERAL] Disable Streaming Replication without restarting either master or slave
> > > Since stopping or restarting the postgres servers would involve complete > > invalidation of the connection pool [Java/JEE app server pool] that may > take > > a few minutes before the application becomes usable, it would be great if > > there is a way we can disable replication [for maintenance reasons like > > applying patches or upgrades, etc]. > > I think even applying patches or upgrades needs restart. > 3. send SIGTERM signal to currently-running walsender process, e.g., by >"select pg_terminate_backend(pid) from pg_stat_replication". Will it be helpful here sending SIGINT instead of killing ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Updateable Views or Synonyms.
On Wed, May 30, 2012 at 12:38 PM, Alban Hertroys wrote: > On 30 May 2012, at 1:16, Tim Uckun wrote: > > > I am wondering if either of these features are on the plate for > > postgres anytime soon? I see conversations going back to 2007 on > > updateable views and some conversations about synonyms but obviously > > they have never been added to the database for some reason or another. > > > You can also create a updateable VIEW using "INSTEAD OF" trigger which is a new feature in PG 9.1. http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
