[GENERAL] Unicode license compatibility with PostgreSQL license
Hi All, For our next set of development activities in PostgreSQL, we want to use the Unicode organization code with PostgreSQL to open source that feature. Is the Unicode license is compatible with PostgreSQL. The following is the header that is present in one of the Unicode files. /* * Copyright 2001-2004 Unicode, Inc. * * Disclaimer * * This source code is provided as is by Unicode, Inc. No claims are * made as to fitness for any particular purpose. No warranties of any * kind are expressed or implied. The recipient agrees to determine * applicability of information provided. If this file has been * purchased on magnetic or optical media from Unicode, Inc., the * sole remedy for any claim will be exchange of defective media * within 90 days of receipt. * * Limitations on Rights to Redistribute This Code * * Unicode, Inc. hereby grants the right to freely use the information * supplied in this file in the creation of products supporting the * Unicode Standard, and to make copies of this file in any form * for internal or external distribution as long as this notice * remains attached. */ Regards, Hari Babu Fujitsu Australia -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installation Size showing huge size in postgres installed on windows os
I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. Any reason why it shows 121GB Regards NS Rajagopla -- View this message in context: http://postgresql.nabble.com/Installation-Size-showing-huge-size-in-postgres-installed-on-windows-os-tp5842522.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] Unicode license compatibility with PostgreSQL license
On Thu, Mar 19, 2015 at 3:03 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: For our next set of development activities in PostgreSQL, we want to use the Unicode organization code with PostgreSQL to open source that feature. Is the Unicode license is compatible with PostgreSQL. The following is the header that is present in one of the Unicode files. I am no lawyer, but FWIW I have never heard of any legal folks I know complain about this license being incompatible with PostgreSQL license. -- 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] Name spacing functions and stored procedures
I guess I see schemas as ways to group data not functions. It would be very nice to be able to group your code into proper modules though. It's something I really miss.
Re: [GENERAL] Reg: PostgreSQL Server base/data recovery
On Thu, Mar 19, 2015 at 5:19 PM, Prajilal KP prajilal...@gmail.com wrote: Recently i have noticed that my PostgreSQL server is returning the error messages like Could not read block 6160722 in file base/data/16384/11033028.47. Don't you have more details to share? There should be some hint here as well, and usually you may be facing such things because of OS or hardware problems. When i have checked the base/data/16384 directory, some of the file's data size is zero and exactly from that time i have started receiving the said error messages. Could you please advise the way to recover the Database without loosing any data? If you are facing hardware problems, this may be a time to deploy a backup and replay WAL up to where you wish to on clean disks. PostgreSQL version : 9.0.4 9.0.4 has been released in 2011, so you are missing 4 years worth of bug fixes, the latest minor version of the 9.0.X release being 9.0.19. Note as well that 9.0 will be EOL at the end of the year, hence you could do even better: an upgrade to a newer major version. Regards, -- 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] Name spacing functions and stored procedures
2015-03-19 11:03 GMT+01:00 Tim Uckun timuc...@gmail.com: I guess I see schemas as ways to group data not functions. It would be very nice to be able to group your code into proper modules though. It's something I really miss. what is advantage modules?
Re: [GENERAL] Name spacing functions and stored procedures
Tim Uckun schrieb am 19.03.2015 um 11:03: I guess I see schemas as ways to group data not functions. A schema is just a namespace. The only link between data and a schema is that data can only live in tables and a table is associated with a namespace. Even if you use it to group data, you actually use to group objects that hold data. It would be very nice to be able to group your code into proper modules though. It's something I really miss. An extension can be seen as a module that lives in a dedicated namespace: the schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reg: PostgreSQL Server base/data recovery
Hi all, Thank you for reading this mail. I need your help to fix the issue with my PostgreSQL server. Recently i have noticed that my PostgreSQL server is returning the error messages like “Could not read block 6160722 in file “base/data/16384/11033028.47”. When i have checked the base/data/16384 directory, some of the file's data size is zero and exactly from that time i have started receiving the said error messages. Could you please advise the way to recover the Database without loosing any data? PostgreSQL version : 9.0.4 OS : RHEL 5.5 regards, prajilal
Re: [GENERAL] Reg: PostgreSQL Server base/data recovery
Thank you Michael for the reply. Here is the other log from pg_log directory ERROR: could not open file base/16384/7969143.26 (target block 13148261): No such file or directory CONTEXT: SQL statement select min(id), max(id) from requests where id last_max_id PL/pgSQL function f_raw_logs_check_for_new_data line 33 at SQL statement STATEMENT: select f_raw_logs_check_for_new_data(1, 10001) ERROR: function f_etl_task_end(integer, integer, integer, integer, unknown) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT: select f_etl_task_end(7161174, 1, 0, 3, E'could not open file base/16384/7969143.26 (target block 13148261): No such file or directory CONTEXT: SQL statement select min(id), max(id) from requests where id last_max_id PL/pgSQL function f_raw_logs_check_for_new_data line 33 at SQL statement ') ERROR: could not open file base/16384/7969143.26 (target block 13148261): No such file or directory When i see check the this file, the file itself exists but the size is 0 byte. The server is writing the whole log in to the mounted network storage, NFS. I have scanned the storage for any errors and nothing found. I will consider your suggestion to upgrade the PostgreSQL version. Regards, Prajilal On Thu, Mar 19, 2015 at 5:46 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Thu, Mar 19, 2015 at 5:19 PM, Prajilal KP prajilal...@gmail.com wrote: Recently i have noticed that my PostgreSQL server is returning the error messages like Could not read block 6160722 in file base/data/16384/11033028.47. Don't you have more details to share? There should be some hint here as well, and usually you may be facing such things because of OS or hardware problems. When i have checked the base/data/16384 directory, some of the file's data size is zero and exactly from that time i have started receiving the said error messages. Could you please advise the way to recover the Database without loosing any data? If you are facing hardware problems, this may be a time to deploy a backup and replay WAL up to where you wish to on clean disks. PostgreSQL version : 9.0.4 9.0.4 has been released in 2011, so you are missing 4 years worth of bug fixes, the latest minor version of the 9.0.X release being 9.0.19. Note as well that 9.0 will be EOL at the end of the year, hence you could do even better: an upgrade to a newer major version. Regards, -- Michael
Re: [GENERAL] Reg: PostgreSQL Server base/data recovery
On Thu, Mar 19, 2015 at 07:02:28PM +0900, Prajilal KP wrote: When i see check the this file, the file itself exists but the size is 0 byte. That suggests you have data corruption, and that you need to restore from backup. The server is writing the whole log in to the mounted network storage, NFS. There are reasons that people get nervous about databases on NFS. Are you ensuring that Postgres fsync() calls (like when COMMIT happens) are not being handled asynchronously? Also, a trivial scan of the release notes in the 9.0.x series shows a number of data corruption fixes since 9.0.4. You should always try to stay on the latest minor release of your version of Postgres. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Installation Size showing huge size in postgres installed on windows os
On 19/03/2015 12:39, jaime soler wrote: El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. Any reason why it shows 121GB Could you share with us \l+ command in psql session ? Also, see what Windows reports as the space taken up by the installation and data directories. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Name spacing functions and stored procedures
2015-03-19 11:46 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com: 2015-03-19 11:03 GMT+01:00 Tim Uckun timuc...@gmail.com: I guess I see schemas as ways to group data not functions. It would be very nice to be able to group your code into proper modules though. It's something I really miss. what is advantage modules? It is terrible hard to implement Oracle PL/SQL modules in Postgres due support more than one language. Pavel
Re: [GENERAL] Reg: PostgreSQL Server base/data recovery
On Thu, Mar 19, 2015 at 8:12 PM, Prajilal KP prajilal...@gmail.com wrote: I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. Well, then, lost data is lost. I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Having fsync = off in postgresql.conf is a perfect method to corrupt your data, you should really not set it to off. -- 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] Reg: PostgreSQL Server base/data recovery
Thank you Andrew for your reply. I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Also i will considering the suggestion to upgrade the PostgreSQL. Regards, Prajilal On Thu, Mar 19, 2015 at 7:39 PM, Andrew Sullivan a...@crankycanuck.ca wrote: On Thu, Mar 19, 2015 at 07:02:28PM +0900, Prajilal KP wrote: When i see check the this file, the file itself exists but the size is 0 byte. That suggests you have data corruption, and that you need to restore from backup. The server is writing the whole log in to the mounted network storage, NFS. There are reasons that people get nervous about databases on NFS. Are you ensuring that Postgres fsync() calls (like when COMMIT happens) are not being handled asynchronously? Also, a trivial scan of the release notes in the 9.0.x series shows a number of data corruption fixes since 9.0.4. You should always try to stay on the latest minor release of your version of Postgres. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Reg: PostgreSQL Server base/data recovery
I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. Well, then, lost data is lost. Oh.. My bad ... I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Having fsync = off in postgresql.conf is a perfect method to corrupt your data, you should really not set it to off. Okay, I will take action immediately to set the fync=on. I hope it with save me for a period. I will consider upgrading to a later version at the earliest.. Thank you very much Prajilal On Thu, Mar 19, 2015 at 8:12 PM, Prajilal KP prajilal...@gmail.com wrote: I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. Well, then, lost data is lost. I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Having fsync = off in postgresql.conf is a perfect method to corrupt your data, you should really not set it to off. -- Michael
Re: [GENERAL] Reg: PostgreSQL Server base/data recovery
I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. Well, then, lost data is lost. Oh.. My bad ... I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Having fsync = off in postgresql.conf is a perfect method to corrupt your data, you should really not set it to off. Okay, I will take action immediately to set the fync=on. I hope it with save me for a period. I will consider upgrading to a later version at the earliest.. Thank you very much Prajilal On Thu, Mar 19, 2015 at 8:24 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Thu, Mar 19, 2015 at 8:12 PM, Prajilal KP prajilal...@gmail.com wrote: I see, As there are many reasons exists for the data corruption i couldn't figure out it exactly. Unfortunately there is no backup for this system and is not possible to restore from the backup. Well, then, lost data is lost. I have been using the NFS storage since 2013 and didn't experience this issue before, now i am afraid. We haven't set to run the fysnc() in our environment. If i set fysnc to on now, did it make any impact to current flow? Having fsync = off in postgresql.conf is a perfect method to corrupt your data, you should really not set it to off. -- Michael
Re: [GENERAL] Name spacing functions and stored procedures
I’ve always used schemas – usually one for each of the business processes. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pavel Stehule Sent: Thursday, March 19, 2015 12:38 AM To: Tim Uckun Cc: pgsql-general Subject: Re: [GENERAL] Name spacing functions and stored procedures Hi 2015-03-19 2:56 GMT+01:00 Tim Uckun timuc...@gmail.commailto:timuc...@gmail.com: What do you guys do to namespace your functions so that they are not jumbled in with the system functions and also somewhat hierarchically organized. Obviously it's possible to create schemas for different namespaces but that can lead to a lot of schemas which hold no data. The other way is to simply name your functions like _lib_etl_csv_import_weird_data_format but that's not too much fun either. What is problem with schemas? It is tool for logical database structuring - for tables, for functions. I use schemas without any problems. Just curious how other people deal with the issue. NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
[GENERAL] Get extensions directory programmatically in another Makefile
Hello, This might seem like an XY problem, but I need to solve it. I am using pgxs to install an extension but I need to refer to the location of the installation directory in another Makefile. How safe is it to assume that MODULE_PATHNAME that I use for CREATE FUNCTION ... AS 'MODULE_PATHNAME' can be used as PG_CONFIG = pg_config PGXS_PKGLIBDIR = $(shell $(PG_CONFIG) --pkglibdir) PGXS_MODULESDIR = $(shell $(PG_CONFIG) --sharedir)/extension in another Makefile? Thanks, Igor
Re: [GENERAL] Installation Size showing huge size in postgres installed on windows os
El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. Any reason why it shows 121GB Could you share with us \l+ command in psql session ? Regards NS Rajagopla -- View this message in context: http://postgresql.nabble.com/Installation-Size-showing-huge-size-in-postgres-installed-on-windows-os-tp5842522.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] Installation Size showing huge size in postgres installed on windows os
El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. Any reason why it shows 121GB Could you share with us \l+ command in psql session ? Regards NS Rajagopla -- View this message in context: http://postgresql.nabble.com/Installation-Size-showing-huge-size-in-postgres-installed-on-windows-os-tp5842522.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Jaime Soler Gómez HOPLA Software EnterpriseDB exclusive distributor ES/PT/IT LatAm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installation Size showing huge size in postgres installed on windows os
I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. Any reason why it shows 121GB Regards NS Rajagopla -- View this message in context: http://postgresql.nabble.com/Installation-Size-showing-huge-size-in-postgres-installed-on-windows-os-tp5842520.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 does one make the following psql statement sql-injection resilient?
On Mon, Mar 16, 2015 at 9:31 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Monday, March 16, 2015, Alvaro Herrera alvhe...@2ndquadrant.com wrote: David G. Johnston wrote: Thanks! I got the gist even with the typo. I actually pondered about prepare/execute after hitting send. Am I correct in remembering that CREATE TEMP TABLE cannot be prepared? I was using the actual query with CREATE TEMP TABLE and then issuing \copy to dump the result out to the file. The limitation of copy to having to be written on a single line makes the intermediary temporary table seem almost a necessity. CREATE TEMP TABLE AS EXECUTE Thanks. Though unless I need to work on the temp table I think: PREPARE ...; \copy (EXECUTE ...) TO '~/temp.csv' ...; Gives the best of all worlds. Except that server COPY only is documented to accept a query that begins with either SELECT or VALUES :( I hereby voice my desire for EXECUTE to be usable as well. David J.
[GENERAL] Re: How does one make the following psql statement sql-injection resilient?
David G. Johnston wrote: Except that server COPY only is documented to accept a query that begins with either SELECT or VALUES :( I hereby voice my desire for EXECUTE to be usable as well. Feel free to submit a patch ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] How does one make the following psql statement sql-injection resilient?
On Thu, Mar 19, 2015 at 12:43 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: David G. Johnston wrote: Except that server COPY only is documented to accept a query that begins with either SELECT or VALUES :( I hereby voice my desire for EXECUTE to be usable as well. Feel free to submit a patch ... I get your point though if anyone else wants this before 2017 they shouldn't count on me. David J.
Re: [GENERAL] regclass and format('%I')
On 15 March 2015 at 08:44, Tom Lane t...@sss.pgh.pa.us wrote:
David G. Johnston david.g.johns...@gmail.com writes:
IOW, as long as the output string matches: ^(?:{2})*$ I do not see
how
it is possible for format to lay in a value at %I that is any more
insecure than the current behavior. If the input string already matches
that pattern then it could be output as-is without any additional risk
and
with the positive benefit of making this case work as expected. The
broken
case then exists when someone actually intends to name their identifier
something which then correctly becomes something on output.
But that's exactly the problem: you just broke a case that used to work.
format('%I') is not supposed to guess at what the user intends; it is
supposed to produce a string that, after being passed through identifier
parsing (dequoting or downcasing), will match the input. It is not
format's business to break that contract just because the input has
already got some double quotes in it.
An example of where this might be important is if you're trying to
construct a query with arbitrary column headers in the output. You
can do
format('... AS %I ...', ..., column_label, ...)
and be confident that the label will be exactly what you've got in
column_label. This proposed change would break that for labels that
happen to already have double-quotes --- but who are we to say that
that can't have been what you wanted?
I agree with Tom that we shouldn't key off of contents in the string to
determine whether or not to quote. Introducing the behave I describe in an
intuitive way would require some kind of type-specific handling in
format(). I'm not sure what the cost of this is to the project, but David
makes the very reasonable point that imposing the burden of choosing
between `%s` and `%I` opens up the possibility of confusing vulnerabilities.
Kind Regards,
Jason Dusek
Re: [GENERAL] Archeiving and Purging
On 03/18/2015 09:20 AM, adityagis wrote: Dear Users, I have lots of data in my DB. I need to do archeiving and purging of my data. Can anyone please help me with step by step riles? Thanks in Advance. Aditya Kumar -- View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. This is where partitioning comes in: https://github.com/keithf4/pg_partman After that is set up, you would only need to drop the partition which would take only a second or so. Problem is, it will probably take awhile to get the data into a partitioned scheme. Of course, I don't know what you mean by lots of data. Normally, just using a delete statement will be fine up until about 100 million rows or so (on decent hardware). --Sam -- 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] Sequences not created, bug in pg_dump?
On 03/19/2015 10:02 AM, Leonardo M. Ramé wrote: El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, Leonardo M. Ramé wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. What version of Postgres? If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they do not show up in the dump file? It works for me here on 9.3. Yes that's the problem. The dump is performed using 9.3.5 on windows. I can not replicate: aklaver@test= create sequence test_seq; aklaver@test= \d List of relations Schema | Name | Type | Owner +-+--+-- public | CamelCap_Quoted | table| aklaver public | app_sessions| table| aklaver public | app_users | table| aklaver public | app_users_vw| view | aklaver public | app_val_session_vw | view | aklaver public | camelcap_not_quoted | table| aklaver public | float_test | table| postgres public | ins_test| table| aklaver public | mytable_is_not_readonly | table| aklaver public | mytable_is_readonly | table| aklaver public | on_duty | table| aklaver public | on_duty_id_seq | sequence | aklaver public | seq_counter | table| aklaver public | t | table| postgres public | t_id_seq| sequence | postgres public | tasks | table| aklaver public | tasks2 | table| aklaver public | tasks_task_id_seq | sequence | aklaver public | tbl_test| table| aklaver public | test_seq| sequence | aklaver /usr/local/pgsql93/bin/pg_dump -T app_sessions -U postgres -p 5452 test test_txt.sql In test_txt.sql: -- -- Name: test_seq; Type: SEQUENCE; Schema: public; Owner: aklaver -- CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; Do you see any warnings/errors when you run the dump? -- Adrian Klaver adrian.kla...@aklaver.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] Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary
17.03.2015 13:22, Sergey Shchukin пишет: 05.03.2015 11:25, Jim Nasby пишет: On 2/27/15 5:11 AM, Sergey Shchukin wrote: show max_standby_streaming_delay; max_standby_streaming_delay - 30s We both need to be more clear about which server we're talking about (master or replica). What are max_standby_streaming_delay and max_standby_archive_delay set to *on the replica*? My hope is that one or both of those is set to somewhere around 8 minutes on the replica. That would explain everything. If that's not the case then I suspect what's happening is there's something running on the replica that isn't checking for interrupts frequently enough. That would also explain it. When replication hangs, is the replication process using a lot of CPU? Or is it just sitting there? What's the process status for the replay process show? Can you get a trace of the replay process on the replica when this is happening to see where it's spending all it's time? How are you generating these log lines? Tue Feb 24 15:05:07 MSK 2015 Stream: MASTER-masterdb:79607161592048 SLAVE:79607161550576 Replay:79607160986064 :: REPLAY 592 KBytes (00:00:00.398376 seconds) Do you see the confl_* fields in pg_stat_database_conflicts on the *replica* increasing? Hi Jim, max_standby_streaming_delay and max_standby_archive_delay both are 30s on master and replica dbs I don't see any specific or heavy workload during this issue with a hanging apply process. Just a normal queries as usual. But I see an increased disk activity during the time when the apply issue is ongoing DSK | sdc | | *busy 61%* | read 11511 | | write 4534 | KiB/r 46 | | KiB/w 4 | MBr/s 52.78 | | MBw/s 1.88 | avq 1.45 | | avio 0.38 ms | DSK | sde | | *busy 60% * | read 11457 | | write 4398 | KiB/r 46 | | KiB/w 4 | MBr/s 51.97 | | MBw/s 1.83 | avq 1.47 | | avio 0.38 ms | DSK | sdd | |*busy 60%* | read9673 | | write 4538 | KiB/r 61 | | KiB/w 4 | MBr/s 58.24 | | MBw/s 1.88 | avq 1.47 | | avio 0.42 ms | DSK | sdj | | *busy 59%* | read9576 | | write 4177 | KiB/r 63 | | KiB/w 4 | MBr/s 59.30 | | MBw/s 1.75 | avq 1.48 | | avio 0.43 ms | DSK | sdh | | *busy 59%* | read9615 | | write 4305 | KiB/r 63 | | KiB/w 4 | MBr/s 59.23 | | MBw/s 1.80 | avq 1.48 | | avio 0.42 ms | DSK | sdf | |*busy 59% * | read9483 | | write 4404 | KiB/r 63 | | KiB/w 4 | MBr/s 59.11 | | MBw/s 1.83 | avq 1.47 | | avio 0.42 ms | DSK | sdi | | *busy 59%* | read 11273 | | write 4173 | KiB/r 46 | | KiB/w 4 | MBr/s 51.50 | | MBw/s 1.75 | avq 1.43 | | avio 0.38 ms | DSK | sdg | | *busy 59%* | read 11406 | | write 4297 | KiB/r 46 | | KiB/w 4 | MBr/s 51.66 | | MBw/s 1.80 | avq 1.46 | | avio 0.37 ms | Although it's not seems to be an upper IO limit. Normally disks are busy at 20-45% DSK | sde | | busy 29% | read 6524 | | write 14426 | KiB/r 26 | | KiB/w 5 | MBr/s 17.08 | | MBw/s 7.78 | avq 10.46 | | avio 0.14 ms | DSK | sdi | | busy 29% | read 6590 | | write 14391 | KiB/r 26 | | KiB/w 5 | MBr/s 17.19 | | MBw/s 7.76 | avq 8.75 | | avio 0.14 ms | DSK | sdg | | busy 29% | read 6547 | | write 14401 | KiB/r 26 | | KiB/w 5 | MBr/s 16.94 | | MBw/s 7.60 | avq 7.28 | | avio 0.14 ms | DSK | sdc | | busy 29% | read 6835 | | write 14283 | KiB/r 27 | | KiB/w 5 | MBr/s 18.08 | | MBw/s 7.74 | avq 8.77 | | avio 0.14 ms | DSK | sdf | | busy 23% | read 3808 | | write 14391 | KiB/r 36 | | KiB/w 5 | MBr/s 13.49 | | MBw/s 7.78 | avq 12.88 | | avio 0.13 ms | DSK | sdd | | busy 23% | read 3747 | | write 14229 | KiB/r 33 | | KiB/w 5 | MBr/s
Re: [GENERAL] Archeiving and Purging
On 03/18/2015 11:38 PM, adityagis wrote: HI David/John, Thanks for the support. But as of now I have not done any configuaration for the archieving process. I hope once my configuarations are done then may be I can proceed with your suggestions. I am still looking for the intial process that need to be performed for archeiving. There is no built in archiving process in Postgres. Archiving, if desired, is left up to the user as there is no 'one way'. Thanks Aditya Kumar -- View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393p5842524.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@aklaver.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] Sequences not created, bug in pg_dump?
El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, Leonardo M. Ramé wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. What version of Postgres? If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they do not show up in the dump file? It works for me here on 9.3. Yes that's the problem. The dump is performed using 9.3.5 on windows. -- 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] Archeiving and Purging
HI David/John, Thanks for the support. But as of now I have not done any configuaration for the archieving process. I hope once my configuarations are done then may be I can proceed with your suggestions. I am still looking for the intial process that need to be performed for archeiving. Thanks Aditya Kumar -- View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393p5842524.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] Unicode license compatibility with PostgreSQL license
On 3/18/2015 11:03 PM, Haribabu Kommi wrote: For our next set of development activities in PostgreSQL, we want to use the Unicode organization code with PostgreSQL to open source that feature. Is the Unicode license is compatible with PostgreSQL. I'm curious... What does this Unicode Inc code do that the existing UTF8 support doesn't ? -- john r pierce, from the mid left coast -- 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] Unicode license compatibility with PostgreSQL license
On Wed, Mar 18, 2015 at 11:03 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: For our next set of development activities in PostgreSQL, we want to use the Unicode organization code with PostgreSQL to open source that feature. Is the Unicode license is compatible with PostgreSQL. Do you mean that you'd like to add ICU support? I think that would be extremely interesting, FWIW. The stability of ICU collations would be quite helpful from a number of different perspective. One of which is that having a contract about the stability of strxfrm()-style binary keys would allow me to make text abbreviated keys exploited in the internal pages of B-Tree indexes, to greatly reduce cache misses with index scans on text attributes. This general technique already been very effective with sorting [1], but it feels likely that we'll need ICU to make the abbreviation technique useful for indexes. [1] http://pgeoghegan.blogspot.com/2015/01/abbreviated-keys-exploiting-locality-to.html -- Regards, Peter Geoghegan -- 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 does one make the following psql statement sql-injection resilient?
On Thu, Mar 19, 2015 at 12:46 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Thu, Mar 19, 2015 at 12:43 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: David G. Johnston wrote: Except that server COPY only is documented to accept a query that begins with either SELECT or VALUES :( I hereby voice my desire for EXECUTE to be usable as well. Feel free to submit a patch ... I get your point though if anyone else wants this before 2017 they shouldn't count on me. While I lack in C language skills I do possess wiki editing skills...ToDo item added. David J.
Re: [GENERAL] Sequences not created, bug in pg_dump?
On 03/19/2015 08:43 AM, Leonardo M. Ramé wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. What version of Postgres? If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they do not show up in the dump file? It works for me here on 9.3. -- Adrian Klaver adrian.kla...@aklaver.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] Installation Size showing huge size in postgres installed on windows os
On 19 March 2015 at 13:44, Raymond O'Donnell r...@iol.ie wrote: On 19/03/2015 12:39, jaime soler wrote: El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for Postgres 9.0 is shown as 121GB. I feel neither the installation or the small postgres databases I would have created use 121GB. For what it's worth, I've seen the wildest claims about installation sizes in that panel. Mostly the installation size is severely underestimated, but overestimates happen too - haven't seen anything this bad though. I stopped trusting those numbers quite a while ago. The reliable method is to check the directory size of your catalog directories and the Postgres installation directory yourself. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequences not created, bug in pg_dump?
Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. -- Leonardo M. Ramé http://leonardorame.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
