Re: [GENERAL] Analyzing last run query in psql
Joe Van Dyk j...@tanga.com wrote: I frequently need to analyze the last query in psql: select * from table where id = 1; explain analyze select * from table where id = 1; It would be nice to be able to do this: explain analyze $LAST (or can I do something like that already?) I'm not using psql interactively, I pipe files into psql (if it matters). There is no history or something like that, but you can use autoexplain. http://www.postgresql.org/docs/9.2/interactive/auto-explain.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Analyzing last run query in psql
On Wed, Jul 3, 2013 at 7:05 AM, Joe Van Dyk j...@tanga.com wrote: I frequently need to analyze the last query in psql: select * from table where id = 1; explain analyze select * from table where id = 1; It would be nice to be able to do this: explain analyze $LAST (or can I do something like that already?) I'm not using psql interactively, I pipe files into psql (if it matters). I don't know of any feature like that, but maybe you can do the following in your script/files: \set lastquery 'your-query-here' so that you can do something like explain :lastquery; But if you are able to manipulate your scripts you can just insert the explain by hand on queries you are interested in, or use autoexplain for all the queries. Luca -- 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] Analyzing last run query in psql
It was my dream to have something we already have in shell - explain analyze !$ I think it should be not very difficult. Oleg On Tue, 2 Jul 2013, Joe Van Dyk wrote: I frequently need to analyze the last query in psql: select * from table where id = 1; explain analyze select * from table where id = 1; It would be nice to be able to do this: explain analyze $LAST (or can I do something like that already?) I'm not using psql interactively, I pipe files into psql (if it matters). Joe Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] seq. DEFAULT values and rules
Is this a bug ?, the new.a_id in my opinion should not be replaced here with nextval () function but with the actual value returned by the sequence. Please have this minimal example . CREATE TABLE a ( a_id serial primary key ); CREATE TABLE b ( b_id serial Primary key ); CREATE TABLE a_b ( a_id INTEGER REFERENCES a (a_id), b_id INTEGER REFERENCES b (b_id) ); INSERT INTO b SELECT n FROM generate_series(1,10) AS n; CREATE RULE a_a_b_insert AS ON INSERT TO a DO INSERT INTO a_b SELECT new.a_id , b_id FROM b; INSERT INTO a (a_id) VALUES (DEFAULT);
Re: [GENERAL] seq. DEFAULT values and rules
salah jubeh s_ju...@yahoo.com writes: Is this a bug ? No, it's a documented, operating-as-designed behavior. Rules are macros and therefore have all the usual multiple-evaluation-of-arguments hazards. If you just want to propagate data from one table to another, a trigger is often a better choice, or at least one with fewer surprises. 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] pg_largeobject.sql script not run after upgrade
On 24/06/2013 19:20, Bruce Momjian br...@momjian.us wrote: On Mon, Jun 24, 2013 at 06:03:40PM +, Stuart Ford wrote: Do you know if not running this script would explain the fact that our dump file sizes have been much smaller than expected? It might be possible if lack of pg_largeobject_metadata values causes your large objects not to be dumped; I have not tested this. This did turn out to be the case in the end, FYI. Dump sizes returned to normal after the permissions were created. Many thanks for your assistance, much appreciated. Stuart This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended only for the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or its content may result in legal liability. If you have received the email in error, please immediately notify us by telephone on +44 333 666 or email gl...@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internet transmission. Any views/opinions expressed within this email and any attachments are that of the individual and not necessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham, B1 1TT. Registered in England Wales. Registered Company No. 06194523. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] V8.4 TOAST table problem
Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order
to take advantage of autovacuum features. This server exists in a very
closed environment (isolated network, limited root privileges; this
explains the older software in use) and runs on RHEL5.5 (i686). After
the upgrade, the database has constantly been growing to the tune of 5-6
GB a day. Normally, the database, as a whole, is ~20GB; currently, it is
~89GB. We have a couple other servers which run equivalent databases and
actually synchronize the records to each other via a 3rd party
application (one I do not have access to the inner workings). The other
databases are ~20GB as they should be.
Running the following SQL, it's fairly obvious there's an issue with a
particular table, and, more specifically, its TOAST table.
|SELECT nspname|| '.' || relnameAS relation,
pg_size_pretty(pg_relation_size(C.oid)) AS size
FROM pg_class C
LEFT JOIN pg_namespace NON (N.oid= C.relnamespace)
WHERE nspnameNOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT20;|
Which produces:
relation | size
--+-
pg_toast.pg_toast_16874 | 89 GB
fews00.warmstates | 1095 MB
...
(20 rows)
This TOAST table is for a table called timeseries which saves large
records of blobbed data. A|SUM(LENGTH(blob)/1024./1024.)| of all the
records in timeseries yields ~16GB for that column. There should be
[b]no reason[/b] this table's TOAST table should be as large as it is.
I've performed a |VACUUM FULL VERBOSE ANALYZE timeseries|, and the
vacuum runs to completion with no errors.
INFO: vacuuming pg_toast.pg_toast_16874
INFO: pg_toast_16874: found 22483 removable, 10475318 nonremovable
row versions in 10448587 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2036 bytes long.
There were 20121422 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
4944885 pages are or will become empty, including 0 at the end of
the table. 4944885 pages containing 0 free bytes are potential move
destinations.
CPU 75.31s/29.59u sec elapsed 877.79 sec.
INFO: index pg_toast_16874_index now contains 10475318 row
versions in 179931 pages
DETAIL: 23884 index row versions were removed.
101623 index pages have been deleted, 101623 are currently reusable.
CPU 1.35s/2.46u sec elapsed 21.07 sec.
REINDEXed the table which freed [b]some[/b] space (~1GB). I can't
CLUSTER the table as there isn't enough space on disk for the process,
and I'm waiting to rebuild the table entirely as I'd like to find out
why it is so much bigger than equivalent databases we have.
Ran a query from the PostgreSQL wiki here - Show Database Bloat
http://wiki.postgresql.org/wiki/Show_database_bloat, and this is what
I get:
current_database | schemaname | tablename | tbloat | wastedbytes |
iname | ibloat | wastedibytes
--++++-+-++--
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_synchlevel
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_localavail
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expirytime
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expiry_null
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | uniq_localintid | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | pk_timeseries | 0.1 | 0
ptrdb04 | fews00 | idx_timeseries_expiry_null | 0.6 | 0 | ? | 0.0 | 0
It looks like the database doesn't consider this space as empty, at
all, but I just don't see where all the disk space is coming from!
I suspect that this database server is deciding to use 4-5x as much disk
space to save the same records pulled from the other data servers. My
question is this: Is there a way I can verify the physical disk size of
a row? I'd like to compare the size of one row on this database to
another healthy database.
Thanks for any help you can provide!
Paul Tilles
[GENERAL] C User Defined Functions
Hey, I was trying to compile a user-defined function in C (I am trying to compile one of the examples given in chapter 35.9 of the 9.3beta2 documentation) using Microsoft Visual Studio Express 2012 on a 64-bit Windows 7 computer. I ran into the following error: C:\cl /I C:\postgres9.3beta2FI\include\server /I C:\postgres9.3beta2FI\include\server /c testFuncss.c Microsoft (R) C/C++ Optimizing Compiler Version 17.00.50727.1 for x64 Copyright (C) Microsoft Corporation. All rights reserved. testFuncss.c c:\postgres9.3beta2fi\include\server\pg_config_os.h(207) : error C2011: 'timezone' : 'struct' type redefinition c:\postgres9.3beta2fi\include\server\pg_config_os.h(207) : see declaration of 'timezone' c:\postgres9.3beta2fi\include\server\pg_config_os.h(216) : error C2011: 'itimerval' : 'struct' type redefinition c:\postgres9.3beta2fi\include\server\pg_config_os.h(216) : see declaration of 'itimerval' I noticed my problem was very similar to the following one: http://www.postgresql.org/message-id/col115-w3057ae644de56e006b9332af...@phx.gbl Therefore, I followed the advice in the response ( http://www.postgresql.org/message-id/1281540487156-2472055.p...@n5.nabble.com) and commented out both the timezone and itimerval structs in pg_config_os.h The compilation then worked fine. I then tried to add my functions into my database using the command given in chapter 35.9 CREATE FUNCTION add_one(integer) RETURNS integer AS 'DIRECTORY/funcs', 'add_one' LANGUAGE C STRICT; Upon doing this I received the error could not load library C:/testFuncss.dll: The specified procedure could not be found. After searching Google for things that might evoke that error, I found out that the problem need not lie in the dll file itself but may lie in something the dll file is calling. I am wondering if the procedure lost relates to the structs commented out. If so, how can I compile the c file without commenting out the structs? If not, what should my next step be in trying to add these functions? Thanks, Jake
[GENERAL] How to create a cursor that is independent of transactions and doesn't calculated when created ?
I wonder how quick is cursor without hold. I have a data set with 10^7 rows. *begin; declare mycursor cursor for select * from z; commit; * - this takes 3 ms. *begin; declare mycursor cursor with hold for select * from z; commit; * - this takes 3 s. Thus, holdable cursor is getting calculated, obviously. I cannot use cursor without hold as it requires a connection per query + client. But I also don't understand - why cursor with hold should start so much longer than cursor without hold. If there any possiblity to have a cursor that as fast as cursor without hold and as transaction-independent as cursor with hold ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-a-cursor-that-is-independent-of-transactions-and-doesn-t-calculated-when-created-tp5762401.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?
boraldomaster wrote If there any possiblity to have a cursor that as fast as cursor without hold and as transaction-independent as cursor with hold ? Depends... From the documentation - the first source of potential understanding: http://www.postgresql.org/docs/9.0/interactive/sql-declare.html In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions. Can you make that copy take considerably less time OR come up with a better implementation? I guess, in theory, you could reduce the startup cost by only creating the temporary file at commit instead of at the declare. In the case where you scroll through the whole cursor once during the transaction as each row is emitted it could also be cached at that point so only un-emitted rows would have to be spooled before the commit could finish. If you provide more of the how/why of what you are doing people may be able to suggest alternative solutions - though 3 seconds for a 10-million row cursor/temporary-table does not seem that hideous. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-a-cursor-that-is-independent-of-transactions-and-doesn-t-calculated-when-created-tp5762401p5762403.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] AWS, cascading replication and WAL archiving
I currently have a working 9.2 master + hot standby setup, using asynchronous replication and WAL archiving (via a shared NFS mount), running in our colocated datacenter. I need to migrate this to AWS, with as little downtime as possible. My plan is to create two cascading standbys, daisy-chained like this: master (M) - primary standby (S1) - secondary standby (S2) - tertiary standby (S3), and at migration time, promote S2 to master and then drop both M and S1 (hope this explanation make sense). WAL-E[1] seems like a perfect solution for WAL archiving on AWS, so I've set the master's archive_command to archive both on NFS (so I don't break the current setup) and on Amazon S3 (using WAL-E) so S2 and S3 can restore from it. Q1) Is this a good migration strategy? Q2) Should I promote S2 before killing M, or should I kill M before promoting S2? Q2) Should S2 and S3 read from the same WAL archive, that's initially written to from M and by S2 when it gets promoted to master; or should I have two separate WAL archives to avoid conflicts Q3) How should I set S2 and S3's recovery.conf so S3 automatically follows S2 when promoted to master? recovery_target_timeline = latest ? Thanks in advance, Daniel Serodio [1] https://github.com/wal-e/wal-e -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] odd locking behaviour
Hi all, I am trying to understand some odd locking behaviour. I apologize in advance if this is a basic question and should be widely understood but I don't see it described in the documentation as far as I could find. I'm using Postgres 8.4.13 I have two tables, call them A B for example purposes. Table A, with column id Table B - foreign key reference a_id matches A.id FULL - some other columns blah1, blah2, blah3 I do this: db1: begin db2: begin db1: select A FOR UPDATE db2: update B set blah1 = 42; --- OK, UPDATE 1 db2: update B set blah2 = 42; --- This blocks waiting for a lock on A!! Here are the exact steps to reproduce: CREATE TABLE A (id bigint NOT NULL); CREATE TABLE B (id bigint NOT NULL, a_id bigint NOT NULL, blah1 bigint, blah2 bigint, blah3 bigint); ALTER TABLE ONLY A ADD CONSTRAINT a__pkey PRIMARY KEY (id); ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL; INSERT INTO A VALUES (1); INSERT INTO B VALUES (1, 1, 1, 2, 3); Now, in two DB connections, CON1 and CON2. CON1: BEGIN; SELECT * FROM A WHERE id = 1 FOR UPDATE; CON2: BEGIN; UPDATE B SET blah1 = 42 WHERE id = 1; UPDATE B SET blah2 = 42 WHERE id = 1; -- this blocks I have verified that if I drop the foreign key constraint requiring B.a_id match A.id that this behaviour does not happen and both updates succeed without blocking. I can perhaps understand why it acquires a shared lock on A when updating B because of the foreign key reference, even though it doesn't seem like it should require it because the columns being updated are not relevant to the foreign key constraint. That behaviour would be less than ideal but at least it would be understandable. However, why does it only try to acquire the lock on the second update If I do a ROLLBACK in CON1, then I see CON2 finish the UPDATE and it acquires a lock on table A. Why? Thank you.
[GENERAL] Feature Idea: Statement Echo in DO$$
I am using a DO$$ $$ block to emulate something that admittedly may be standard practice to accomplish using psql but for which I am using a less capable UI. Anyway, the basic form is: DO $$ DECLARE some_var varchar := 'value'; BEGIN UPDATE . WHERE col = some_var; UPDATE . WHERE col = some_var; RETURN; END; $$ Now because of the DO I have no idea how many records were affected for each of the UPDATE statements. I am thinking that, either at the statement-level or even somehow defined inside the DO, some way to have the system automatically echo the usual 0 records updated message out of the DO. i.e., UPDATE (VERBOSE) SET id = some_var; GET DIAGNOSTICS obviously works, and for a stored function is probably the better option, but for a simple DO oriented script command it is quite verbose. There is a prior discussion (somewhere) regarding using STRICT without RETURNING in similar situations to easily define when only one (and only one) record is expected to be affected. This thought falls into the same usability category. Thoughts? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Feature-Idea-Statement-Echo-in-DO-tp5762454.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot connect to remote postgres database
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:
postgres=# \du
List of roles
Role name | Attributes | Member of
---+-+---
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DB
I assigned passwords using alter role etc..
The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
hostall all 198.204.114.0/24 md5
I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user username
I tried changing md5 to password and pam without success. Onlt
trust works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.
Any hints on where to start looking?
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326
--
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] Cannot connect to remote postgres database
Stephen Carville wrote:
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:
If you want to evaluate PostgreSQL, you should evaluate v9.2. 8.4 is
pretty ancient and lacks lots of cool features so your comparison won't
be fair to PostgreSQL.
postgres=# \du
List of roles
Role name | Attributes | Member of
---+-+---
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DB
I assigned passwords using alter role etc..
Which exact ALTER ROLE did you use? Feel free to redact the actual
password, of course.
The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
hostall all 198.204.114.0/24 md5
I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user username
I tried changing md5 to password and pam without success. Onlt
trust works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.
md5 is the standard. password is plain text (which you don't want)
and pam will try to authenticate against OS users, which is probably
not what you want.
Any hints on where to start looking?
Is there any NAT happening between the client and the server? Check the
server's log for a LOG: connection received: host=x.x.x.x message so
you can check which IP is reaching the server.
Regards,
Daniel Serodio
--
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] Memory usage after upgrade to 9.2.4
I found where the problem is:
http://www.postgresql.org/docs/9.1/static/release-9-1-6.html
It could be nice if something is added to the 9.2 release notes to warn the
admins.
2013/4/24 Adrian Klaver adrian.kla...@gmail.com
On 04/24/2013 11:20 AM, Daniel Cristian Cruz wrote:
I've done an explain analyze under the test environment, and there is no
aggressive memory usage.
So I dropped the database in the new cluster and restored a fresh dump
from production (in theory, that's the difference between the two
environments).
Some minutes after I got an answer: after a dump / restore, there is no
problem. The same cluster just a dump/restore.
Since I had no idea on what is the problem, and the structure from the
dump of the bad database is equal to the dump from the production (it
differs only in check constraints where ((turma.situacao)::text = ANY
((ARRAY['Aguardando Atualização'::character varying, 'Em
Andamento'::character varying])::text[]))) became
((turma.situacao)::text = ANY (ARRAY[('Aguardando
Atualização'::character varying)::text, ('Em Andamento'::character
varying)::text], how can I report a issue in pg_upgrade?
The bug reporting page is here:
http://www.postgresql.org/**support/submitbug/http://www.postgresql.org/support/submitbug/
Thanks, Adrian, Tomas and Tom. It's a specific issue, it's on backup
until May, 1st. Until there, if someone would like to know something
about it, just ask me, I would like to help removing an issue.
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
--
Adrian Klaver
adrian.kla...@gmail.com
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Re: [GENERAL] Analyzing last run query in psql
On Wed, Jul 3, 2013 at 6:43 AM, Oleg Bartunov o...@sai.msu.su wrote: It was my dream to have something we already have in shell - explain analyze !$ It would probably be: explain analyze !! (at least in bash syntax) Joe I think it should be not very difficult. Oleg On Tue, 2 Jul 2013, Joe Van Dyk wrote: I frequently need to analyze the last query in psql: select * from table where id = 1; explain analyze select * from table where id = 1; It would be nice to be able to do this: explain analyze $LAST (or can I do something like that already?) I'm not using psql interactively, I pipe files into psql (if it matters). Joe Regards, Oleg __**__**_ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Analyzing last run query in psql
I'd like the execution plan to be in the psql output, not in the postgres log. On Tue, Jul 2, 2013 at 11:20 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Joe Van Dyk j...@tanga.com wrote: I frequently need to analyze the last query in psql: select * from table where id = 1; explain analyze select * from table where id = 1; It would be nice to be able to do this: explain analyze $LAST (or can I do something like that already?) I'm not using psql interactively, I pipe files into psql (if it matters). There is no history or something like that, but you can use autoexplain. http://www.postgresql.org/docs/9.2/interactive/auto-explain.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot connect to remote postgres database
On 07/03/2013 01:30 PM, Daniel Serodio (lists) wrote:
Stephen Carville wrote:
I have been asked to evaluate Oracle, mysql and postgresql as a possible
replacement for our existing Oracle and MsSQL databases. Oracle and
mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
cooperative.
I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
listening on all available interfaces and netstat confirms this. I
created an additional user for the postgres db:
If you want to evaluate PostgreSQL, you should evaluate v9.2. 8.4 is
pretty ancient and lacks lots of cool features so your comparison won't
be fair to PostgreSQL.
I figured it out.
I used tshark to capture the traffic. When I looked at the packets I
could see that I was asking for the wrong database!
This does not work:
# psql -h scadev02.lereta.com -U stephen
This does:
# psql -h scadev02.lereta.com -U stephen postgres
I guess it's always easy once you know the answer.
postgres=# \du
List of roles
Role name | Attributes | Member of
---+-+---
postgres | Superuser | {}
: Create role
: Create DB
stephen | Superuser | {}
: Create role
: Create DB
I assigned passwords using alter role etc..
Which exact ALTER ROLE did you use? Feel free to redact the actual
password, of course.
The problem is that no authentication method except trust seems to work.
in pg_hba.conf:
local all all trust
hostall all 198.204.114.0/24 md5
I've tried both of the above users and get the same error each time:
psql: FATAL: password authentication failed for user username
I tried changing md5 to password and pam without success. Onlt
trust works As near as I can tell by reading the documentation, it is
setup correctly but I have, obviously, done something wrong.
md5 is the standard. password is plain text (which you don't want)
and pam will try to authenticate against OS users, which is probably
not what you want.
Any hints on where to start looking?
Is there any NAT happening between the client and the server? Check the
server's log for a LOG: connection received: host=x.x.x.x message so
you can check which IP is reaching the server.
Regards,
Daniel Serodio
--
Stephen Carville
Apprentice Cook and Bottle Washer
Lereta LLC
1-800-537-3821 X1326
--
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] Cannot connect to remote postgres database
On 07/03/2013 01:27 PM, Andrew Sullivan wrote: Nothin' for nothin', but . . . On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote: I have the software (v 8.4.13) installed on 64 bit Centos 6. It is Why for a new project would you select such an old release of the software? Convenience. It is already there. Anyways, I figured it out. I used tshark to capture the traffic. When I looked at the packets I could see that I was asking for the wrong database! This does not work: # psql -h scadev02.lereta.com -U stephen This does: # psql -h scadev02.lereta.com -U stephen postgres I guess it's always easy once you know the answer. I tried changing md5 to password and pam without success. Onlt trust works As near as I can tell by reading the documentation, it is setup correctly but I have, obviously, done something wrong. Any hints on where to start looking? If this is the packaged software for the OS, you probably need to look at the package-specific notes about the authentication methods. But your experience does seem pretty strange: looking at what you did _appears_ like it ought to work. Are you sure you have the right file? Sometimes distributions put them in funky places. A -- Stephen Carville Apprentice Cook and Bottle Washer Lereta LLC 1-800-537-3821 X1326 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] async streaming and recovery_target_timeline=latest
We have an async streaming setup using 9.1.9 and 3 nodes - let's call them A, B, and C. A is the master, B and C are slaves. Today, A crashed, so we made B be the master and told C to follow along with the switch by changing the primary_conninfo in it's recovery.conf, making sure the history file had made it to the WAL archive, then restarting it. That's worked very well for us in the past, but not so much today. When C came back online, it started complaining about missing WALs: 2013-07-03T21:22:42.441347+00:00 pgdb41-vpc postgres[25779]: [18184-1] db=,user= LOG: shutting down 2013-07-03T21:22:42.457728+00:00 pgdb41-vpc postgres[25779]: [18185-1] db=,user= LOG: database system is shut down 2013-07-03T21:22:46.852845+00:00 pgdb41-vpc postgres[28942]: [1-1] db=,user= LOG: database system was shut down in recovery at 2013-07-03 21:22:42 UTC 2013-07-03T21:22:46.866127+00:00 pgdb41-vpc postgres[28947]: [1-1] db=[unknown],user=[unknown] LOG: incomplete startup packet 2013-07-03T21:22:47.368871+00:00 pgdb41-vpc postgres[28942]: [2-1] db=,user= LOG: restored log file 0010.history from archive 2013-07-03T21:22:47.413588+00:00 pgdb41-vpc postgres[28956]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:47.767182+00:00 pgdb41-vpc postgres[28942]: [3-1] db=,user= LOG: restored log file 0010.history from archive 2013-07-03T21:22:47.767289+00:00 pgdb41-vpc postgres[28942]: [4-1] db=,user= LOG: entering standby mode 2013-07-03T21:22:47.930394+00:00 pgdb41-vpc postgres[28978]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:48.410056+00:00 pgdb41-vpc postgres[28942]: [5-1] db=,user= LOG: redo starts at 1469/A2604868 2013-07-03T21:22:48.445921+00:00 pgdb41-vpc postgres[28986]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:48.962090+00:00 pgdb41-vpc postgres[28994]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:49.477279+00:00 pgdb41-vpc postgres[29020]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:49.993021+00:00 pgdb41-vpc postgres[29027]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:22:50.508848+00:00 pgdb41-vpc postgres[29034]: [1-1] db=postgres,user=postgres FATAL: the database system is starting up 2013-07-03T21:23:30.651775+00:00 pgdb41-vpc postgres[28942]: [6-1] db=,user= LOG: consistent recovery state reached at 146A/14FFFA8 2013-07-03T21:23:30.651805+00:00 pgdb41-vpc postgres[28942]: [7-1] db=,user= LOG: invalid magic number in log file 5226, segment 1, offset 5242880 2013-07-03T21:23:30.653214+00:00 pgdb41-vpc postgres[28917]: [1-1] db=,user= LOG: database system is ready to accept read only connections 2013-07-03T21:23:31.123588+00:00 pgdb41-vpc postgres[29754]: [2-1] db=,user= LOG: streaming replication successfully connected to primary 2013-07-03T21:23:31.123647+00:00 pgdb41-vpc postgres[29754]: [3-1] db=,user= FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 0010146A0001 has already been removed At this point, my understanding of postgres must be wrong, because it appears to me that the slave is looking for WAL 146A/01 because that's where it reached consistent state. However, that was in the previous timeline - we didn't get to the 10 history timeline till 146A/0C: # cat 0010.history 15 000F146A000Cno recovery target specified Shouldn't postgres know to be looking for 000F146A0001, not 0010146A0001? I'm trying to see what part of our process we have wrong to have ended up in this state but I'm missing it. For what it's worth the new master (node B) certainly seems to have all the WAL files you might expect. Here's some snippets of an ls -l, but all the files are there in between the snippets. -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F1469009F -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A0 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A1 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A2 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A3 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A4 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A5 -rw--- 1 postgres postgres 16777216 Jul 3 21:13 000F146900A6 . . . -rw--- 1 postgres postgres 16777216 Jul 3 21:15 000F146A -rw--- 1 postgres postgres 16777216 Jul 3 21:15 000F146A0001 - the consistent state seems to be found here -rw--- 1 postgres postgres 16777216 Jul 3 21:15 000F146A0002 -rw--- 1 postgres postgres 16777216 Jul 3 21:15 000F146A0003 -rw--- 1
Re: [GENERAL] (Default) Group permissions
On 07/02/2013 03:35 AM, Albe Laurenz wrote: Michael Orlitzky wrote: I want to be able to create a database, set up the (default) group permissions, and have them work, even when a new user is added to one of the groups. Right now I don't know of a way to get default group permissions. There is none, as far as I can say. You have two options: - You have the default privileges on the roles, and every user has to run SET ROLE to the role before he or she creates objects. - Whenever you create a new user, you set default privileges for the user. I guess that the first solution is not useful until there are event triggers for database logins. Thanks for taking a look. The first isn't manageable because it requires users to do things. The second is what I've decided on for now, but the way that I know which default privileges to set is to look up his group membership by hand and set his default privileges on the databases where the group should have them. I'd love it if that step could be automated. -- 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] Cannot connect to remote postgres database
2013/7/4 Stephen Carville scarvi...@lereta.com: On 07/03/2013 01:27 PM, Andrew Sullivan wrote: Nothin' for nothin', but . . . On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote: I have the software (v 8.4.13) installed on 64 bit Centos 6. It is Why for a new project would you select such an old release of the software? Convenience. It is already there. yes, but you should to know, so 8.4 will be unsupported at July 2014 Regards Pavel Stehule Anyways, I figured it out. I used tshark to capture the traffic. When I looked at the packets I could see that I was asking for the wrong database! This does not work: # psql -h scadev02.lereta.com -U stephen This does: # psql -h scadev02.lereta.com -U stephen postgres I guess it's always easy once you know the answer. I tried changing md5 to password and pam without success. Onlt trust works As near as I can tell by reading the documentation, it is setup correctly but I have, obviously, done something wrong. Any hints on where to start looking? If this is the packaged software for the OS, you probably need to look at the package-specific notes about the authentication methods. But your experience does seem pretty strange: looking at what you did _appears_ like it ought to work. Are you sure you have the right file? Sometimes distributions put them in funky places. A -- Stephen Carville Apprentice Cook and Bottle Washer Lereta LLC 1-800-537-3821 X1326 -- 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
