[GENERAL] Restoring 2 Tables From All Databases Backup
Dear all, 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. Please let me know how to extract the tables from this 10Gb backup file Thanks -- 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 can i get record by data block not by sql?
H... Something funny here... Starting from http://ieeexplore.ieee.org/stamp/stamp.jsp?tp=arnumber=4609383 or googling IMine: Index Support for Item Set Mining baralis one gets very slightly more information. Cheers Kev -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig Ringer Sent: 04 October 2011 14:04 To: 姜头; Postgres General Subject: Re: [GENERAL] How can i get record by data block not by sql? Joining several reply threads. Replies inline below. On 10/04/2011 05:07 PM, 姜头 wrote: I found the Gist is difficult to understand. :) I will try my best to read it. I find GiST hard to understand too. It's probably the easiest way to add a custom index type, though. I strongly recommend that you start reading here if you want to develop additional functionality for PostgreSQL's backends: http://developer.postgresql.org/pgdocs/postgres/internals.html particularly: http://developer.postgresql.org/pgdocs/postgres/indexam.html http://developer.postgresql.org/pgdocs/postgres/gist.html http://developer.postgresql.org/pgdocs/postgres/storage.html On 10/04/2011 05:01 PM, 姜头 wrote: I am sorry for my poor english and come from a non-english country. The original paper can be download this time for IEEE service is unavailable and i find the another paper which is sent to you as attached file. In section 2.1.1 I-Tree which the researcher implement in postgresql using *blocks.* My Chinese [?] isn't so great either ;-) so there's no need to apologise. A *very* quick look at the paper you sent suggests that they might've been working on an index-oriented table (covering index) structure for data mining. That's interesting. They don't talk much about their implementation or publish source code, though :-( The paper is talking about PostgreSQL blocks, ie what PostgreSQL's BLOCKSIZE refers to. These are (usually) 8kb chunks of files on a regular file system, stored within the datadir, and are accessed via pread() and pwrite() by the PostgreSQL backends and managed in the buffer cache. (For other readers: the paper is T.SUNITHA, G.SRUJANA P.V.RAVIKANTH, IMine: Index Support for Item Set Mining. International Journal of Computer Trends and Technology- July to Aug Issue 2011, pp255-261. ISSN: 2231-2803. ) On 10/04/2011 04:39 PM, 姜头 wrote: Thank you very much. I read the paper http://dbdmg.polito.it/twiki/bin/view/Public/IMine again carefully and find that they don't explain clearly. I think they say 'blocks' means 'blocks of dababase(DBMS)'. We know that dbms will form their own blocks which is not file blocks. Yep, I'd say so. Actually ,they and me want to record the phycical address of data The offset of data within a PostgreSQL database file, yes. and then we can form a disk-resident tree. (like tree in memory using pointer,but this time it residents on disk.) Yep. I know access blocks is hard as you say. I havn't know now and rowid of record in orcale can be used? it sounds like phycial address more. You'd want to use the block index then the offset within the block, like the btree index already does. Have a look at how the btree index code works. I don't know pread() more and i will study it now. It turns out you don't want pread() etc anyway. You probably want to use PostgreSQL's own data access functions. I was talking about pread() because that's the low level system call PostgreSQL uses to read its data, and it is one of the system calls you can use for raw I/O on device nodes. It turns out that's not what you want to do at all. You want to do I/O on PostgreSQL database files, you just want to define your own storage structure and index structure. The first step to doing this will probably be to read the source code of the btree index, and read the documentation I linked to. The single most helpful thing will probably be to get the source code of the sample implementation made by one of the authors of the papers you're interested in. I don't know if that'll be possible, but I'm sure it'd help a lot if you could do it. Thank you very very much. Best wishes. -- 原始邮件 -- *发件人:* Craig Ringerring...@ringerc.id.au; *发送时间:* 2011年10月4日(星期二) 下午3:15 *收件人:* 姜头104186...@qq.com; *主题:* Re: re： [GENERAL] How can i get record by data block not by sql? Data block isn't a term with one fixed meaning. You could be referring to Pg blocks, file system blocks, disk sectors, or all sorts of other things. Do you actually mean raw disk sectors? If so: on Linux and most other UNIXes you can use block I/O calls to access them just like files on a file system by opening the device node. pread and friends should be just fine. You sound like you might want direct I/O, in which case look at the O_DIRECT flag. There is also async I/O. You still don't really explain WHY you want this or what you're trying to achieve. It sounds like you're
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
On Wednesday, October 5, 2011, Thomas Kellerer spam_ea...@gmx.net wrote: Dave Page, 04.10.2011 21:46: We updated our build system to use BitRock 7 today (for unrelated reasons) which has new features for ACL management. We're going to investigate replacing cacls/icacls with those features tomorrow and will create some test builds ASAP. If you can provide the test builds publicly, I will be happy to test them and see if that behaves differently on my system. Thanks, we will. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [GENERAL] Postgresql-8.2 Replication
On 05/10/2011 09:46, khizer wrote: Hi, Kindly requesting u to send the steps to set up the Postgresql-8.2 database replication. 8.2 doesn't have built-in replication - you'll need to use Slony or one of the other tools. See the docs for suggestions: http://www.postgresql.org/docs/8.2/static/high-availability.html 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] Postgresql-8.2 Replication
On 05/10/2011 11:19, khizer wrote: Thanks for reply Can we use slony on ubuntu OS as it was giving error while compiling and installing slony Please keep your replies on the list. What errors were you getting? If I recall correctly, you need to have the headers for PostgreSQL on the machine where you're building Slony. Anyway, if you post your errors here others may be able to help you. one more thing there is no package for slony8.2 isn't it I don't know - 8.2 is getting old now, so maybe. It probably depends on what version of Ubuntu you're using. Ray. So any other steps to Replicate postgresql8.2 databse on ubuntu On 10/05/2011 03:44 PM, Raymond O'Donnell wrote: On 05/10/2011 09:46, khizer wrote: Hi, Kindly requesting u to send the steps to set up the Postgresql-8.2 database replication. 8.2 doesn't have built-in replication - you'll need to use Slony or one of the other tools. See the docs for suggestions: http://www.postgresql.org/docs/8.2/static/high-availability.html 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] Postgresql-8.2 Replication
On 05/10/2011 12:04, khizer wrote: Raymond i have attached the error file as above. You really should keep your replies on the list - some of the many experts on the list may be able to help you. I'm out of my depth on this one, but the error message looks to me as if you're missing the PostgreSQL header files, as suggested in my last email. If you post the output to the list, someone else will be able to say for sure. 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
[GENERAL] Fwd: [BUGS] BUG #6240: About - postgreswdinit.sql
Hi, In a freshly commissioned h/w, when watchdog is created for the first time, I faced some problem. I never faced this problem earlier. Currenlty we are using the following version: export PGODBC_VERSION=psqlodbc-09.00.0300 Postgres version=9.0.4 Logs are attached here: Jan 1 02:05:09.492736 info CLA-0 FSPostgresWD: COMMUTIL INFO connection to db server succeeded, Postgres is up Jan 1 02:05:09.520838 info CLA-0 FSPostgresWD: COMMUTIL INFO create WD DB postgreswd Jan 1 02:05:09.521774 info CLA-0 FSPostgresWD: COMMUTIL INFO port = 5432 Jan 1 02:05:09.526093 info CLA-0 FSPostgresWD: COMMUTIL INFO = executing: /opt/nokiasiemens/SS_Postgres/bin/psql -d template1 -v ON_ERROR_STOP=on -f /opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql -q -h 127.0.0.4 -p 5432 as user: _qnrnwdbman Jan 1 02:05:10.531239 warn CLA-0 postgres[9919]: [2-1] ERROR: could not create file base/16384/11500: File exists[This error is from postgres server] Jan 1 02:05:10.531317 warn CLA-0 postgres[9919]: [2-2] STATEMENT: create database postgreswd; Jan 1 02:05:10.536788 err CLA-0 FSPostgresWD: COMMUTIL ERROR psql: /opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql returned with error: 3 Jan 1 02:05:10.536851 err CLA-0 FSPostgresWD: COMMUTIL ERROR failed to execute /opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql got error 3 Jan 1 02:05:10.536895 err CLA-0 FSPostgresWD: COMMUTIL ERROR failed to initialize PersDBProxy object Jan 1 02:05:10.536943 info CLA-0 FSPostgresWD: COMMUTIL INFO DB postgreswd does not exist Jan 1 02:05:10.546575 err CLA-0 postgres[9922]: [2-1] FATAL: database postgreswd does not exist Jan 1 02:05:10.547846 warn CLA-0 FSPostgresWD: COMMUTIL WARNING === Sql response : connection to SQL server rc code : -1 Jan 1 02:05:10.547903 info CLA-0 FSPostgresWD: COMMUTIL INFO [unixODBC]FATAL: database postgreswd does not exist (210) Jan 1 02:05:10.547947 warn CLA-0 FSPostgresWD: COMMUTIL WARNING ### failed to connect to the DB Jan 1 02:05:10.547994 info CLA-0 FSPostgresWD: COMMUTIL INFO initializing DB Jan 1 02:05:10.548920 info CLA-0 FSPostgresWD: COMMUTIL INFO postgreswd starting with redundancy model 1 current role 0 Jan 1 02:05:10.572467 info CLA-0 FSPostgresWD: COMMUTIL INFO connection to db server succeeded, Postgres is up Jan 1 02:05:10.598632 info CLA-0 FSPostgresWD: COMMUTIL INFO create WD DB postgreswd ……….. ………. Kindly let me know as early as possible. Thanks in advance Regards Chethan HB Life is just a moment between a breath and none. Hope I do enough to add meaning to it.
[GENERAL] Postgresql Data directory Issue
Dear all, I have a database server ( 10 databases near about 110 GB) running Postgresql-.8.3 ) Today I need to format that system but I an facing the below issues :- 1. I am trying to use the previous data directory (/opt/PostgresPlus/8.3/data) in new Postgresql-8.4 installation but it results in below error while installing :- Data Directory [/media/a519e535-62a8-43a2-af93-2323690a3177/opt/PostgresPlus/8.3/data]: Error: The existing data directory (catalog version: 200711281) is not compatible with this server (catalog version: 200904091). Press [Enter] to continue : So i think I need to install Postgresql-8.3 to use that data. 2. I installed Postgresql-8.3 in new server and trying to use the data directory but it faces the below error :- Existing data directory is not empty and it cannot able to use it . Please note that I am not able to take the complete databases backup because the database server is down and restarts when we start backups. Any help will be appreciated. Thanks -- 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] Postgresql Data directory Issue
* Adarsh Sharma wrote: 2. I installed Postgresql-8.3 in new server and trying to use the data directory but it faces the below error :- Existing data directory is not empty and it cannot able to use it . First, be very, very careful. initdb already saved your data from destruction once, but you should never have allowed it anywhere near your live data in the first place. Also, do not tempt fate: Make sure that your new installation is as close to identical to the old one as possible (architecture, endianness, word size). Starting PostgreSQL on an incompatible data directory should fail reliably, but it sounds as if you do not have current backups. If your data is on some sort of shared storage, maybe you can take a snapshot before you do any more experiments? Install with a different data directory (or make sure it is not mounted while you are installing), then reconfigure before starting the database. -- Christian -- 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] Postgresql Data directory Issue
Sorry to be rude, but you are in urgent need of a PostgreSQL DBA, (if you feel you somehow value your data). Anyways, 8.3 catalog is not compatible with 8.4. You need to dump-upgrade-restore if you just start 8.3 server with /usr/local/pgsql/bin/postgres -D path_to_your_data_dir what happens? Στις Wednesday 05 October 2011 15:05:26 ο/η Adarsh Sharma έγραψε: Dear all, I have a database server ( 10 databases near about 110 GB) running Postgresql-.8.3 ) Today I need to format that system but I an facing the below issues :- 1. I am trying to use the previous data directory (/opt/PostgresPlus/8.3/data) in new Postgresql-8.4 installation but it results in below error while installing :- Data Directory [/media/a519e535-62a8-43a2-af93-2323690a3177/opt/PostgresPlus/8.3/data]: Error: The existing data directory (catalog version: 200711281) is not compatible with this server (catalog version: 200904091). Press [Enter] to continue : So i think I need to install Postgresql-8.3 to use that data. 2. I installed Postgresql-8.3 in new server and trying to use the data directory but it faces the below error :- Existing data directory is not empty and it cannot able to use it . Please note that I am not able to take the complete databases backup because the database server is down and restarts when we start backups. Any help will be appreciated. Thanks -- Achilleas Mantzios -- 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] Postgresql-8.2 Replication
On 05/10/2011 12:24, khizer wrote: ok Thank u but as i am new bie i am not aware abour mailing list of postgresql :-) But that's what you emailed to originally - the general mailing list for PostgreSQL. See here: http://www.postgresql.org/community/lists Make sure you do Reply-all, so that your replies go to the list as well as to the person who replied to you; that way everyone subscribed to the list sees them, and is in a position to help you. 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
[GENERAL] gaps/overlaps in a time table : current and previous row question
hello, let's say that each rows in a table contains a start time and a end time (timeinterval type), but the index are not ordered nor consecutive, e.g : $ select * from T order by starttime index | starttime| endtime -+-+- 3| t1 | t2 1| t3 | t4 18 | t5 | t6 12 | t7 | t8 I want a result that shows time gaps and overlaps in this table, that is : delta -+ t3 - t2 | t5 - t4 | t7 - t6 | how would I do that ? I guess this could be done with window function and lag() function but I don't know exactly how. Any suggestion ? thanks I guess my question is more about -- 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 one column into another in same table?
I don't this this is possible as postgres.
There is something simular with:
alter table table_name alter column column_foo using column_bar
But I don't think there's any performance advantage over a simple
update and the using clause doesn't appear to have an equivalent in an
add column statement.
You could.
alter table table_name rename column_foo to column_bar;
alter table table_name add column_foo foo_data_type default =
nextval('new_foo_sequence');
This has your best chance of success since renaming a column should
not have to touch every row of the table.
Regards
On 4 October 2011 20:21, J.V. jvsr...@gmail.com wrote:
What I need to do is to save the id column for future use and then modify
the id column resetting all values from another sequence.
So I need to select the id column or somehow get the data into another
column in the same table.
And then I can update the id column (after dropping the constraint).
J.V.
On 10/4/2011 1:09 PM, Scott Marlowe wrote:
On Tue, Oct 4, 2011 at 12:24 PM, J.V.jvsr...@gmail.com wrote:
Currently I can select one column into another with two statements:
alter tabletable_name add column id_old int;
updatetable_name set id_old = id;
Is there a way to do this in one statement with a select into? I have
tried
various select statements but want the new column (with the same data) to
be
in the same table and to have it execute much more quickly that the two
statements currently do.
Do you need another column or do you just want to alter a column that
already exists? If so you can alter a column from one type to another
and throw a using clause at it to convert the data in some way. I
think we need to know a bit better what you're trying to do.,
--
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
[GENERAL] null values in a view
Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; Thanks -Lauri Kajan -- 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] gaps/overlaps in a time table : current and previous row question
2011/10/5 thomas veymont thomas.veym...@gmail.com hello, let's say that each rows in a table contains a start time and a end time (timeinterval type), there is no such type ( no result for select * from pg_type where typname ~ 'timeinterval' ). can you show exact table structure (output of psql \d or better, CREATE TABLE command)? but the index are not ordered nor consecutive, e.g : $ select * from T order by starttime index | starttime| endtime -+-+- 3| t1 | t2 1| t3 | t4 18 | t5 | t6 12 | t7 | t8 I want a result that shows time gaps and overlaps in this table, that is : delta -+ t3 - t2 | t5 - t4 | t7 - t6 | how would I do that ? I guess this could be done with window function and lag() function but I don't know exactly how. Any suggestion ? -- assuming that you actually want lag compared to previous starttime - try this: select index, starttime, endtime, starttime - lag(endtime) over(order by starttime asc) as delta from test; PS. this question should probably go to pgslq-sql mailing list more than pgsql-general. also please give more details next time. Thanks.
Re: [GENERAL] null values in a view
try: CREATE OR REPLACE VIEW view1 AS SELECT name as a1, null::text as a2, 'test'::text as a3 FROM some_table; Στις Wednesday 05 October 2011 17:22:21 ο/η Lauri Kajan έγραψε: Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; Thanks -Lauri Kajan -- Achilleas Mantzios -- 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] null values in a view
On Wed, Oct 05, 2011 at 05:22:21PM +0300, Lauri Kajan wrote: Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? using normal cast: null::text as a2 or cast(null as text); Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Restoring 2 Tables From All Databases Backup
2011/10/5 Adarsh Sharma adarsh.sha...@orkash.com: 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
Re: [GENERAL] null values in a view
Lauri Kajan lauri.ka...@gmail.com writes: This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; FYI, the syntax typename 'literal' works *only* with string literals, not anything else. For any other target you have to write CAST(value AS typename) or equivalently value::typename. CAST is SQL-standard, :: is a Postgres-ism. 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] how to disable all pkey/fkey constraints globally
Create a temp table that will store all the foreign kez constraints then create a function that add an entry to this table then write a plpgsql function that reads the constraints and disable them write another function to read the constraints from the temporary tables and create them again have a look on pg_constraints , pg_tables regards From: Joe Abbate j...@freedomcircle.com To: pgsql-general@postgresql.org Sent: Wednesday, October 5, 2011 11:27 AM Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally On 10/05/2011 04:49 AM, depst...@alliedtesting.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of J.V. Sent: Tuesday, October 04, 2011 10:00 PM To: pgsql-general Subject: [GENERAL] how to disable all pkey/fkey constraints globally Is there a generic way to drop just all primary key and foreign key constraints on a given table? I know how to do given the specific name of the constraint. same question but one statement that would just disable all primary key and foreign key constraints on a given database? and am assuming the reverse could not be done because would have to re- create each one individually? Maybe I do not want to drop, so is there a way to simply disable all globally (not drop) then enable all globally? You can find all foreign key constraints for a given table, save constraint definitions, drop constraints, and later re-enable them. Look into table pg_constraint and function pg_get_constraintdef. If you'll allow me to toot my horn, here's an alternative: - Use dbtoyaml [1] to output your tables to a file, say, yaml1 - Edit the yaml1 file, searching for primary_key and foreign_keys and remove those you want to drop, save the result to a different file, say, yaml2 - Use yamltodb [2] with yaml2 to generate SQL to drop the primary keys and foreign keys, in the correct dependency order (at least that's what it's supposed to do, make sure you use the -1 option), to a file, say, sql1 - Run sql1 through psql to drop the constraints - Use yamltodb with yaml1 to generate SQL to recreate the primary keys and foreign keys to, say sql2 - Run sql2 through psql to recreate the constraints Regards, Joe [1] http://www.pyrseas.org/docs/dbtoyaml.html [2] http://www.pyrseas.org/docs/yamltodb.html -- 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] Restoring 2 Tables From All Databases Backup
2011/10/5 Adarsh Sharma adarsh.sha...@orkash.com
Dear all,
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.
Please let me know how to extract the tables from this 10Gb backup file
since this is a plaintext file, not a custom format backup,
you unfortunately need to extract portions of text using some editor or
program...
for this kind of work I would recommend Perl or Awk.
below is my first shot - thats incomplete (no restoration of
indexes/sequences):
gunzip -cd all.sql.gz | awk '/^CREATE TABLE mytable /,/^$/ { print }; /^COPY
mytable /,/^$/ { print };'
which does print all lines from CREATE TABLE mytable to next empty line, and
all lines from COPY mytable to next empty line.
[GENERAL] How to create database link and synonym in postgresql 9.0
Hi, In one of the migration porject want to access and update some tables from other database on same postgresql server. The question is how to access tables from other database on the same postgres server. If it is by database link like Oracle then what is the syntax. And how to create synonym? Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2. db2 has table emp which i want to access from db1. so i create a dblink in oracle named dblnk_emp and access the table in db1 by select * from emp@dblnk_emp; so i create synonym for emp@dblnk_emp as emp in db1. In postgres I can access table from other database by dblink but cannot update it. Also there is support for synonym. Please help..
[GENERAL] function XXX already exists with same argument types
Hello, I use PostgreSQL 8.4 under CentOS 5.7: # rpm -qa | grep post compat-postgresql-libs-4-1PGDG.rhel5 postgresql-8.4.9-1PGDG.rhel5 postgresql-server-8.4.9-1PGDG.rhel5 compat-postgresql-libs-4-1PGDG.rhel5 postgresql-libs-8.4.9-1PGDG.rhel5 postgresql-devel-8.4.9-1PGDG.rhel5 And perform nightly backups with this cronjob: 1 1* * *pg_dump $PGDATABASE | gzip -c $HOME/backups/pref-`date +\%F`.sql.gz I also restored them on the same and another machines (dev. VMs) often and w/o problems. Now I'm trying to migrate to another machine and CentOS 6 and suddenly emits the warnings: # rpm -qa|grep post postgresql-libs-8.4.7-2.el6.x86_64 postgresql-devel-8.4.7-2.el6.x86_64 postgresql-8.4.7-2.el6.x86_64 postgresql-docs-8.4.7-2.el6.x86_64 postgresql-server-8.4.7-2.el6.x86_64 pref# \i pref-2011-10-05-a.sql SET SET SET SET SET SET psql:pref-2011-10-05-a.sql:16: ERROR: language plpgsql already exists ALTER LANGUAGE SET CREATE DOMAIN ALTER DOMAIN CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION psql:pref-2011-10-05-a.sql:195: ERROR: function pref_update_catch already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:236: ERROR: function pref_update_game already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:256: ERROR: function pref_update_hand already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:288: ERROR: function pref_update_luck already exists with same argument types ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION psql:pref-2011-10-05-a.sql:339: ERROR: function pref_update_match already exists with same argument types ALTER FUNCTION The 1st waring is ok, as I've run create language plpgsql before. But why do I get the function warings? In my original database I don't see any duplicates with \df or \df+ # psql psql (8.4.9) Type help for help. pref= \df pref_update_catch List of functions Schema | Name| Result data type | Argument data types | Type +---+--+ + public | pref_update_catch | void | _id character varying, _trix0 i nteger, _trix1 integer, _trix2 integer, _trix3 integer, _trix4 integer, _trix5 i nteger, _trix6 integer, _trix7 integer, _trix8 integer, _trix9 integer, _trix10 integer, _trust integer | normal (1 row) Same picture in my target database on CentOS 6.0 (it has 8.4.7 and not 8.4.9 though) Any ideas what is happening please? Regards Alex -- 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] Restoring 2 Tables From All Databases Backup
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 lis...@guedesoft.net: 2011/10/5 Adarsh Sharma adarsh.sha...@orkash.com: 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] function XXX already exists with same argument types
On Oct 5, 2011, at 11:21, Alexander Farber wrote: Hello, I use PostgreSQL 8.4 under CentOS 5.7: # rpm -qa | grep post compat-postgresql-libs-4-1PGDG.rhel5 postgresql-8.4.9-1PGDG.rhel5 postgresql-server-8.4.9-1PGDG.rhel5 compat-postgresql-libs-4-1PGDG.rhel5 postgresql-libs-8.4.9-1PGDG.rhel5 postgresql-devel-8.4.9-1PGDG.rhel5 And perform nightly backups with this cronjob: 1 1* * *pg_dump $PGDATABASE | gzip -c $HOME/backups/pref-`date +\%F`.sql.gz I also restored them on the same and another machines (dev. VMs) often and w/o problems. Now I'm trying to migrate to another machine and CentOS 6 and suddenly emits the warnings: # rpm -qa|grep post postgresql-libs-8.4.7-2.el6.x86_64 postgresql-devel-8.4.7-2.el6.x86_64 postgresql-8.4.7-2.el6.x86_64 postgresql-docs-8.4.7-2.el6.x86_64 postgresql-server-8.4.7-2.el6.x86_64 pref# \i pref-2011-10-05-a.sql SET SET SET SET SET SET psql:pref-2011-10-05-a.sql:16: ERROR: language plpgsql already exists ALTER LANGUAGE SET CREATE DOMAIN ALTER DOMAIN CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION psql:pref-2011-10-05-a.sql:195: ERROR: function pref_update_catch already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:236: ERROR: function pref_update_game already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:256: ERROR: function pref_update_hand already exists with same argument types ALTER FUNCTION psql:pref-2011-10-05-a.sql:288: ERROR: function pref_update_luck already exists with same argument types ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION psql:pref-2011-10-05-a.sql:339: ERROR: function pref_update_match already exists with same argument types ALTER FUNCTION The 1st waring is ok, as I've run create language plpgsql before. But why do I get the function warings? Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and they're getting added to the new database when it's created. Michael Glaesemann grzm seespotcode net -- 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 XXX already exists with same argument types
Thank you Michael, but no - On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann g...@seespotcode.net wrote: psql:pref-2011-10-05-a.sql:339: ERROR: function pref_update_match already exists with same argument types ALTER FUNCTION Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and they're getting added to the new database when it's created. # psql -U postgres -W template1 Password for user postgres: psql (8.4.7) Type help for help. template1=# \df List of functions Schema | Name | Result data type | Argument data types | Type +--+--+-+-- (0 rows) -- 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] 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 pasma...@gmail.com 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 lis...@guedesoft.net: 2011/10/5 Adarsh Sharma adarsh.sha...@orkash.com: 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] How to create database link and synonym in postgresql 9.0
There is no CREATE SYNONYM in PostgreSQL and it's not planned for
implementation.
There is also no direct support for foreign data wrapper. But it's planned
for 9.2.
Nearest that you can get with PostgreSQL 9.0 is cautious use of dblink and
views and rules.
here's a sample script to show what I mean:
create database db1;
create database db2;
\c db2;
create table emp ( id integer primary key, name text);
insert into emp values (1,'Joe'), (2,'Mary');
\c db1
\i /usr/share/postgresql/9.0/contrib/dblink.sql
SELECT dblink_connect( 'db2', 'dbname=db2' );
-- remote SELECT;
CREATE VIEW emp AS SELECT id,name FROM dblink('db2','SELECT id,name FROM
emp') AS emp(id integer, name text);
SELECT * FROM emp ORDER BY name;
-- remote INSERT:
CREATE RULE emp_ins AS ON INSERT TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'INSERT INTO emp(id,name) VALUES(' || quote_nullable(NEW.id) || ',' ||
quote_nullable(NEW.name) || ')', true );
INSERT INTO emp VALUES(3,'Phil');
-- remote UPDATE:
CREATE RULE emp_upd AS ON UPDATE TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'UPDATE emp SET id=' || quote_nullable(NEW.id) || ', name=' ||
quote_nullable(NEW.name)
|| ' WHERE id=' || quote_nullable(OLD.id), true );
UPDATE emp SET name = 'Philip' WHERE id = 3;
2011/10/5 tushar nehete tpneh...@gmail.com
Hi,
In one of the migration porject want to access and update some tables from
other database on same postgresql server.
The question is how to access tables from other database on the same
postgres server.
If it is by database link like Oracle then what is the syntax.
And how to create synonym?
Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2.
db2 has table emp which i want to access from db1.
so i create a dblink in oracle named dblnk_emp and access the table in db1
by
select * from emp@dblnk_emp;
so i create synonym for emp@dblnk_emp as emp in db1.
In postgres I can access table from other database by dblink but cannot
update it. Also there is
support for synonym.
Please help..
Re: [GENERAL] null values in a view
This worked. Thank you all! I know the casting is quite basic operation but could this be added to the CREATE VIEW documentation? Now there is only an example: CREATE VIEW vista AS SELECT text 'Hello World' AS hello; The same syntax won't work with other type as Tom wrote. -Lauri On Wed, Oct 5, 2011 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lauri Kajan lauri.ka...@gmail.com writes: This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; FYI, the syntax typename 'literal' works *only* with string literals, not anything else. For any other target you have to write CAST(value AS typename) or equivalently value::typename. CAST is SQL-standard, :: is a Postgres-ism. 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
[GENERAL] I/O error on data file, can't run backup
Running postgresql 9.0.5 on balapapa ~ # uname -a Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux I'm trying to run pg_dump on my database, and get an error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not read block 1 in file base/612249/11658: Inn/ut-feil pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM pg_opfamily I have tried to stop postgresql and take a filesystem backup of the data directory with a cp -ax, but it crashes on the same file. I've looked at the directory with ls -l, and the file looks pretty normal to me. I've also rebooted from a live CD and run fsck on my /var partition, and it doesn't find any problem. The database is still working perfectly. The backup script overwrote my previous backup with a 40 byte file (yes silly me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a recent backup anymore. Is this fixable? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Analytic type functionality, matching patters in a column then increment an integer
I can do this in excel with vba, though due to the volume of data that is now impracticable and I am trying to move most of my logic into the query and db for analysis. Looking at the analytic functions I see no way to carry values over the way they need to be. Example column: I have a column that evaluates to either tf,tt,ft,ff (true false matches). tf means the start of a section, tt mans intermediary but in the good section ft means the end of the section that needs to be marked. ff is just ignored so I can have 'evaluation' tf tt ft ff ff tf ft and would like to have a column indicate like this: 'evaluation''indicator' tf 1 tt 1 ft 1 ff ff tf 2 ft 2 tf 3 tt 3 ft 3 ff I have tried rank() and some case statements though I can quite seem to get the sql to be aware across rows as shown in the desired indicator column noted above. It seems like I am missing something that would we aware like that. Any ideas? Thanks in advance.
Re: [GENERAL] I/O error on data file, can't run backup
Leif Biberg Kristensen l...@solumslekt.org writes: Running postgresql 9.0.5 on balapapa ~ # uname -a Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux I'm trying to run pg_dump on my database, and get an error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not read block 1 in file base/612249/11658: Inn/ut-feil pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM pg_opfamily I have tried to stop postgresql and take a filesystem backup of the data directory with a cp -ax, but it crashes on the same file. You have a disk failure on some sector of that file, apparently. I'd be thinking about replacing that disk drive if I were you. Once it starts showing uncorrectable errors the MTTF is going to be short. The backup script overwrote my previous backup with a 40 byte file (yes silly me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a recent backup anymore. Is this fixable? Postgres can't magically resurrect data that your drive lost, if that's what you were hoping for. However, you might be in luck, because that file is probably just an index and not original data. Try this: select relname from pg_class where relfilenode = 11658; On my 9.0 installation I get pg_opclass_am_name_nsp_index. If you get the same (or any other index for that matter) just reindex that index and you'll be all right ... or at least, you will be if that's the only file your drive has lost. 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] I/O error on data file, can't run backup
On Wednesday 5. October 2011 20.42.00 Tom Lane wrote: Postgres can't magically resurrect data that your drive lost, if that's what you were hoping for. However, you might be in luck, because that file is probably just an index and not original data. Try this: select relname from pg_class where relfilenode = 11658; On my 9.0 installation I get pg_opclass_am_name_nsp_index. If you get the same (or any other index for that matter) just reindex that index and you'll be all right ... or at least, you will be if that's the only file your drive has lost. Tom, this is what I get: postgres@balapapa ~ $ psql pgslekt psql (9.0.5) Type help for help. pgslekt=# select relname from pg_class where relfilenode = 11658; relname - pg_opfamily (1 row) regards, Leif -- 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] I/O error on data file, can't run backup
I seemingly fixed the problem by stopping postgres and doing: balapapa 612249 # mv 11658 11658.old balapapa 612249 # mv 11658.old 11658 And the backup magically works. I'm gonna move the data to another disk right now. regards, Leif -- 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] Analytic type functionality, matching patters in a column then increment an integer
On 05/10/11 19:29, Henry Drexler wrote: and would like to have a column indicate like this: 'evaluation' 'indicator' tf 1 tt 1 ft 1 ff ff tf 2 ft 2 tf 3 tt 3 ft 3 ff SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over (order by id) FROM tfcount ORDER BY id; id | evaluation | sum ++- 1 | tf | 1 2 | tt | 1 3 | ft | 1 4 | ff | 1 5 | ff | 1 6 | tf | 2 7 | ft | 2 8 | tf | 3 9 | tt | 3 10 | ft | 3 11 | ff | 3 (11 rows) OK, so that's almost it, but you'd like ff to be null. You probably can do it with a suitably nested CASE, but it's probably clearer as a sub-query. SELECT id, evaluation, CASE WHEN evaluation='ff' THEN null::int ELSE sum::int END AS section_num FROM ( SELECT id, evaluation, sum(case when evaluation='tf' then 1 else 0 end) over (order by id) FROM tfcount ) AS rows ORDER BY id; HTH P.S. - I always find the windowing function syntax confusing, but it's as the standards define I believe. -- Richard Huxton Archonet Ltd -- 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] Analytic type functionality, matching patters in a column then increment an integer
that was spot on Richard. Thank you for your time and the solution. On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton d...@archonet.com wrote: On 05/10/11 19:29, Henry Drexler wrote: and would like to have a column indicate like this: 'evaluation' 'indicator' tf 1 tt 1 ft 1 ff ff tf 2 ft 2 tf 3 tt 3 ft 3 ff SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over (order by id) FROM tfcount ORDER BY id; id | evaluation | sum ++- 1 | tf | 1 2 | tt | 1 3 | ft | 1 4 | ff | 1 5 | ff | 1 6 | tf | 2 7 | ft | 2 8 | tf | 3 9 | tt | 3 10 | ft | 3 11 | ff | 3 (11 rows) OK, so that's almost it, but you'd like ff to be null. You probably can do it with a suitably nested CASE, but it's probably clearer as a sub-query. SELECT id, evaluation, CASE WHEN evaluation='ff' THEN null::int ELSE sum::int END AS section_num FROM ( SELECT id, evaluation, sum(case when evaluation='tf' then 1 else 0 end) over (order by id) FROM tfcount ) AS rows ORDER BY id; HTH P.S. - I always find the windowing function syntax confusing, but it's as the standards define I believe. -- Richard Huxton Archonet Ltd
Re: [GENERAL] I/O error on data file, can't run backup
Leif Biberg Kristensen l...@solumslekt.org writes: I seemingly fixed the problem by stopping postgres and doing: balapapa 612249 # mv 11658 11658.old balapapa 612249 # mv 11658.old 11658 And the backup magically works. Wow, that is magic. I was going to suggest copying pg_opfamily from template0, which would probably work (maybe requiring reindexing) as long as you didn't have any non-core data types in use. But you got lucky. I'm gonna move the data to another disk right now. Good plan. 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] I/O error on data file, can't run backup
On Wednesday 5. October 2011 22.41.49 Tom Lane wrote: Leif Biberg Kristensen l...@solumslekt.org writes: I'm gonna move the data to another disk right now. Good plan. Couple of things I forgot to mention, in case it matters: The disk is a 1 TB Seagate Barracuda S-ATA, and it has been in use for about a year. I've been using this brand since way back around 1998 without any problems, but have never used any disk more than 3 years. The file system is ext3. I had a hang on the machine a few hours earlier that required a power-off reboot. That has been a problem with this rig since I built it about a year ago, it's probably a funky connection somewhere. This may be the direct cause of the I/O error, which also may mean that the disk is not to blame. I'm so used to postgres and everything else coming up without a hiccup after a power-off that I don't usually pay much attention to it. But I'm certainly going to rework my backup strategy, and keep several generations. regards, Leif -- 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] I/O error on data file, can't run backup
On 10/05/2011 02:48 PM, Leif Biberg Kristensen wrote: I had a hang on the machine a few hours earlier that required a power-off reboot. That has been a problem with this rig since I built it about a year ago, it's probably a funky connection somewhere. This may be the direct cause of the I/O error, which also may mean that the disk is not to blame. I'm so used to postgres and everything else coming up without a hiccup after a power-off that I don't usually pay much attention to it PostgreSQL is great, but it can't overcome defective hardware. I'm thinking perhaps a funky memory problem - you are having odd crashes after all. If memory is failing you could have a file that is corrupted not on disk but in the cache. Perhaps in the process of stopping and starting PostgreSQL, the data that was causing the trouble got flushed from cache then reread from disk. You may find this story interesting: http://blogs.oracle.com/ksplice/entry/attack_of_the_cosmic_rays1 Cheers, Steve -- 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] gaps/overlaps in a time table : current and previous row question
I think you need to get the full list of change dates first. Assuming you're searching over a time period between period_from and period_to: SELECT change_time, sum(diff) as total_diff FROM ( SELECT starttime as change_time, 1 AS diff FROM t WHERE starttime period_from AND endtime period_to UNION ALL SELECT endtime as change_time, -1 AS diff FROM t WHERE endtime period_from AND endtime period_to ) a GROUP BY change_time HAVING sum(diff) 0 ORDER BY change_time asc I used this in a pgplsql function to produce a very simular result to what you were looking for. You need to start by finding how many time periods overlapped period_from, then accumulatively add on total_diff for each row you process. Hope this helps. 2011/10/5 Filip Rembiałkowski plk.zu...@gmail.com: 2011/10/5 thomas veymont thomas.veym...@gmail.com hello, let's say that each rows in a table contains a start time and a end time (timeinterval type), there is no such type ( no result for select * from pg_type where typname ~ 'timeinterval' ). can you show exact table structure (output of psql \d or better, CREATE TABLE command)? but the index are not ordered nor consecutive, e.g : $ select * from T order by starttime index | starttime | endtime -+-+- 3 | t1 | t2 1 | t3 | t4 18 | t5 | t6 12 | t7 | t8 I want a result that shows time gaps and overlaps in this table, that is : delta -+ t3 - t2 | t5 - t4 | t7 - t6 | how would I do that ? I guess this could be done with window function and lag() function but I don't know exactly how. Any suggestion ? -- assuming that you actually want lag compared to previous starttime - try this: select index, starttime, endtime, starttime - lag(endtime) over(order by starttime asc) as delta from test; PS. this question should probably go to pgslq-sql mailing list more than pgsql-general. also please give more details next time. Thanks. -- 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] I/O error on data file, can't run backup
On Thursday 6. October 2011 00.17.38 Steve Crawford wrote: I'm thinking perhaps a funky memory problem - you are having odd crashes after all. I've been thinking about the memory myself, but it passes memtest86plus with flying colors. Or at least it did the last time I checked which is a few months ago. The problems got a lot better after I replaced a monster Radeon XFX video card with a very basic fanless NVidia card (with the added bonus that I can now actually watch Flash videos in full screen), which may point to overheating issues. In other news: I discovered that injecting `date +%u` into the backup file name at an appropriate place will number it by weekday, which is great for keeping daily backups for a week. regards, Leif. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Selecting All Columns Associated With Maximum Value of One Column
A table (chemistry) has columns named site_id, sample_date, param, quant, and str_name (among other columns). I want to find the site_id, sample_date, and quant for a specific str_name and param. I cannot get the proper syntax in the SELECT statement. My attempts are variations of, SELECT max(quant), param, site_id, sample_date, str_name from chemistry WHERE param = 'TDS' AND str_name = 'BurrowCrk'; which prompts postgres to tell me, ERROR: column chemistry.param must appear in the GROUP BY clause or be used in an aggregate function I suspect that retrieving these data requires nested SELECT statements, and I'd appreciate learning how to retrive such data. 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] Selecting All Columns Associated With Maximum Value of One Column
On Oct 5, 2011, at 19:34, Rich Shepard rshep...@appl-ecosys.com wrote: A table (chemistry) has columns named site_id, sample_date, param, quant, and str_name (among other columns). I want to find the site_id, sample_date, and quant for a specific str_name and param. I cannot get the proper syntax in the SELECT statement. My attempts are variations of, SELECT max(quant), param, site_id, sample_date, str_name from chemistry WHERE param = 'TDS' AND str_name = 'BurrowCrk'; which prompts postgres to tell me, ERROR: column chemistry.param must appear in the GROUP BY clause or be used in an aggregate function I suspect that retrieving these data requires nested SELECT statements, and I'd appreciate learning how to retrive such data. Rich Max is an aggregate function and thus requires one of: 1) GROUP BY 2) Window - max(quant) OVER (PARTITION BY ...) To be present in the query. A correlated sub-select would work but you would still need to use group by and you would not gain anything in this particular scenario. They each have their own usage scenarios and your description is not sufficient to determine which one you need; but likely adding an appropriate GROUP BY clause will get you what you want. Also, while the warning only specifies the param field all the other field will give you the same error if you use the GROUP BY claus. The Window syntax affects just the aggregate function and so only that single field would need to be modified BUT the window clause results in all records remaining in the final query whereas the GROUP BY clause effectively removes duplicates. David J. -- 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] Problem with pg_upgrade 9.0 - 9.1
Thomas Kellerer wrote: Thomas Kellerer, 17.09.2011 12:32: I was trying to upgrade my Postgres 9.0 installation using pg_upgrade. Running it first with --check revealed no problems. The when I did the actual migration, the following happened: Mismatch of relation id: database dellstore, old relid 83613, new relid 16530 Failure, exiting I now got the same error (alas with a different relation id) while migrating a completely different data directory. Anything I can do to help find the reason for this problem (or bug?) Unfortuantely the data contains some confidential information so I cannot make it available. This bug was fixed just after 9.1.1 was released. The bug is that Windows doesn't properly pass the right flags for the oid set functions to operate. If you can compile the git 9.1.X current, the fix is in there; the fix will be in 9.1.2. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] I/O error on data file, can't run backup
On 10/05/2011 03:43 PM, Leif Biberg Kristensen wrote: On Thursday 6. October 2011 00.17.38 Steve Crawford wrote: I'm thinking perhaps a funky memory problem - you are having odd crashes after all. I've been thinking about the memory myself, but it passes memtest86plus with flying colors. Or at least it did the last time I checked which is a few months ago. I have had two machines pass extensive memtest86plus but fail on heavy pgbench testing and in both cases the cause was ultimately traced to bad memory. Cheers, Steve -- 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] Selecting All Columns Associated With Maximum Value of One Column
On Wed, Oct 5, 2011 at 7:34 PM, Rich Shepard rshep...@appl-ecosys.comwrote: A table (chemistry) has columns named site_id, sample_date, param, quant, and str_name (among other columns). I want to find the site_id, sample_date, and quant for a specific str_name and param. I cannot get the proper syntax in the SELECT statement. My attempts are variations of, SELECT max(quant), param, site_id, sample_date, str_name from chemistry WHERE param = 'TDS' AND str_name = 'BurrowCrk'; which prompts postgres to tell me, ERROR: column chemistry.param must appear in the GROUP BY clause or be used in an aggregate function I suspect that retrieving these data requires nested SELECT statements, and I'd appreciate learning how to retrive such data. Rich Based on your subject line, I'm guessing that you want something like this: select quant, param, site_id, sample_date, str_name from chemistry where param = 'TDS' and str_name = 'BurrowCrk' and quant = (select max(quant) from chemistry where param = 'TDS' and str_name = 'BurrowCrk')
Re: [GENERAL] Create Extension search path
Roger Niederland ro...@niederland.com writes: On my windows install of postgres 9.1.0. I have a search_path set in the config file. This search path has several schemas defined. Some of the databases within postgres, do not have the schema specified on the search path defined within the database. Trying to add pgcryto via: CREATE EXTENSION pgcrypto; or CREATE EXTENSION pgcrypto SCHEMA public; Fail with the error invalid value for parameter search_path. To add the extension required me to change the search_path. I've committed a fix for this. Thanks for the report! (As pointed out in the thread, there are probably better ways to do what you're doing with the search path, but in any case it's not real desirable for CREATE EXTENSION to fail if the current setting is wonky.) 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
[GENERAL] plpgsql: type of array cells
I'm trying to use an array of objects in plpgsql (postgresql 8.4): drop type if exists test_t cascade; create type test_t AS ( i integer, s text ); create or replace function test2() RETURNS SETOF test_t AS $$ DECLARE arr test_t ARRAY[3]; tmp test_t; BEGIN FOR i in 1 .. 3 LOOP -- ok. Can write to test_t.i tmp.i:=i; -- ok. Can assign a cell from arr to a test_t object arr[i]:=tmp; -- error: arr[i].i=3; RETURN NEXT tmp; END LOOP; END; $$ LANGUAGE plpgsql; I'm getting an error when attempting to interpret this function: NOTICE: drop cascades to function test2() ERROR: syntax error at or near . LINE 21: arr[i].i=3; ^ ** Error ** ERROR: syntax error at or near . SQL state: 42601 Character: 272 Isn't arr[i] of type test_t?? Thanks, Amit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql: type of array cells
I'm trying to use an array of objects in plpgsql (postgresql 8.4): drop type if exists test_t cascade; create type test_t AS ( i integer, s text ); create or replace function test2() RETURNS SETOF test_t AS $$ DECLARE arr test_t ARRAY[3]; tmp test_t; BEGIN FOR i in 1 .. 3 LOOP -- ok. Can write to test_t.i tmp.i:=i; -- ok. Can assign a cell from arr to a test_t object arr[i]:=tmp; -- error: arr[i].i=3; RETURN NEXT tmp; END LOOP; END; $$ LANGUAGE plpgsql; I'm getting an error when attempting to interpret this function: NOTICE: drop cascades to function test2() ERROR: syntax error at or near . LINE 21: arr[i].i=3; ^ ** Error ** ERROR: syntax error at or near . SQL state: 42601 Character: 272 Isn't arr[i] of type test_t?? Thanks, Amit -- 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] I/O error on data file, can't run backup
On 10/06/2011 03:06 AM, Leif Biberg Kristensen wrote: I seemingly fixed the problem by stopping postgres and doing: balapapa 612249 # mv 11658 11658.old balapapa 612249 # mv 11658.old 11658 And the backup magically works. Woo! That's ... interesting. I'd be inclined to suspect filesystem corruption, a file system bug / kernel bug (not very likely if you're on ext3), flakey RAM, etc rather than a failing disk ... though a failing disk _could_ still be the culprit. Use smartmontools to do a self-test; if 'smartctl -d ata -t long /dev/sdx' (where 'x' is the drive node) is reported by 'smartctl -d ata -a /dev/sdx' as having passed, there are no pending or uncorrectable sectors, and the disk status is reported as 'HEALTHY' your disk is quite likely OK. Note that a 'PASSED' or 'HEALTHY' report by its self doesn't mean much, disk firmwares often return HEALTHY even when the disk can't even read sector 0. I strongly recommend making a full backup, both a pg_dump *and* a file-system level copy of the datadir. Personally I'd then do a test restore of the pg_dump backup on a separate Pg instance and if it looked OK I'd re-initdb then reload from the dump. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Postgresql-8.2 Replication
Hi Experts, I want to set Master Slave replication of a database on Ubuntu 10.10 So kindly requesting u to send the instructions. Please waiting for your valuable responses I tried to compile and install slony but i am getting error for installation as follows; ***mehdi***@**mehdi-desktop**:~$ pg_config BINDIR = /usr/lib/postgresql/8.2/bin DOCDIR = /usr/share/doc/postgresql-doc-8.2 INCLUDEDIR = /usr/include/postgresql PKGINCLUDEDIR = /usr/include/postgresql INCLUDEDIR-SERVER = /usr/include/postgresql/8.2/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib/postgresql/8.2/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/postgresql/8.2/man SHAREDIR = /usr/share/postgresql/8.2 SYSCONFDIR = /etc/postgresql PGXS = /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--libexecdir=/usr/lib/postgresql-8.2' '--disable-maintainer-mode' '--disable-dependency-tracking' '--srcdir=.' '--mandir=/usr/share/postgresql/8.2/man' '--with-docdir=/usr/share/doc/postgresql-doc-8.2' '--datadir=/usr/share/postgresql/8.2' '--bindir=/usr/lib/postgresql/8.2/bin' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-krb5' '--with-openssl' '--with-gnu-ld' '--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4' '--with-includes=/usr/include/tcl8.4' '--with-pgport=5432' 'CFLAGS=-g -O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed' 'CC=cc' 'CPPFLAGS=' 'build_alias=x86_64-linux-gnu' CC = cc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/tcl8.4 CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed LDFLAGS_SL = LIBS = -lpgport -lpam -lssl -lcrypto -lkrb5 -lcom_err -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.2.7 ***root***@**mehdi-desktop**:~/slony/slony1-1.2.22# ./configure --prefix=/usr/lib/postgresql/8.2/bin/ --with-pgconfigdir=/usr/lib/postgresql/8.2/bin/ --with-perltools=/usr/lib/postgresql/8.2/bin/ --with-pgpkglibdir=/usr/lib/postgresql/8.2/lib/ --with-pgsharedir=/usr/share/postgresql/8.2/ checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking which template to use... linux configure: using CFLAGS= checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for ld used by GCC... /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... yes checking for perl... /usr/bin/perl checking for tar... /bin/tar checking for flex... no checking for ,... no checking for lex... no checking for bison... bison -y checking for sed... sed checking for the pthreads library -lpthreads... no checking whether pthreads work without any flags... no checking whether pthreads work with -Kthread... no checking whether pthreads work with -kthread... no checking for the pthreads library -llthread... no checking whether pthreads work with -pthread... yes checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE checking if more special flags are required for pthreads... no checking for cc_r... gcc checking how to run the C preprocessor... gcc -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking fcntl.h usability... yes checking fcntl.h presence... yes checking for fcntl.h... yes checking limits.h usability... yes checking limits.h presence... yes checking for limits.h... yes checking stddef.h usability... yes checking stddef.h presence... yes checking for stddef.h... yes checking sys/socket.h usability... yes checking sys/socket.h presence... yes checking for sys/socket.h... yes checking sys/time.h usability... yes checking sys/time.h presence... yes checking for sys/time.h... yes checking for inttypes.h... (cached) yes checking for gettimeofday... yes checking for dup2... yes checking for alarm... yes checking for memset... yes checking for select... yes checking for strdup... yes checking for
[GENERAL] user-interface to upload csv files
Hi, I am having problems getting csv files into postgres. Does anyone know if there is an opensource user-interface to tackle this? yours, Rob
Re: [GENERAL] Fwd: Postgresql-8.2 Replication
On Wed, Oct 5, 2011 at 10:41 PM, khizer khi...@srishtisoft.com wrote: ***root***@**mehdi-desktop**:~/slony/slony1-1.2.22# make make[1]: Entering directory `/home/mehdi/slony/slony1-1.2.22/src' make[2]: Entering directory `/home/mehdi/slony/slony1-1.2.22/src/xxid' gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../.. -I/usr/include/postgresql/ -I/usr/include/postgresql/8.2/server/ -c -o xxid.o xxid.c make[2]: Leaving directory `/home/mehdi/slony/slony1-1.2.22/src/xxid' make[1]: Leaving directory `/home/mehdi/slony/slony1-1.2.22/src' So does the make finish? Does sudo make install then work? -- 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] user-interface to upload csv files
On 10/06/2011 01:47 PM, Robert Buckley wrote: Hi, I am having problems getting csv files into postgres. Does anyone know if there is an opensource user-interface to tackle this? If it's a well-behaved CSV file, use the COPY command or psql's \copy. For more complicated work, use an ETL tool like Talend, look into pg_bulkload, or write some perl/python script to do it. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
