on connections) - but database corruption is not.
Before doing ANYTHING else, read
http://wiki.postgresql.org/wiki/Corruption and act on it.
How big is the DB?
What file system is it on?
PostgreSQL 9.0.[what?] ?
Host OS?
Disk subsystem?
--
Craig Ringer http://www
ago, but it hasn't come up recently.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
should be handing those already.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
the server is stopped. See
http://wiki.postgresql.org/wiki/Corruption .
--
Craig Ringer
;
PERFORM setval(
quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 0);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Change + 0 to whatever your increment is.
--
Craig Ringer
, but it was not
helpful.
SELECT relpersistence FROM pg_class WHERE relname = 't1';
'u' is unlogged, 'p' is persistent, 't' is temporary.
See http://www.postgresql.org/docs/current/static/catalog-pg-class.html
http://www.postgresql.org/docs/9.2/static/catalog-pg-class.html
--
Craig Ringer
On 11/01/2012 10:05 PM, Terry Khatri wrote:
Somebody PLEASE HELP ! is Tome Lane around !
http://www.postgresql.org/support/professional_support/
.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On 11/01/2012 01:10 PM, Scott Ribe wrote:
On Oct 31, 2012, at 8:50 PM, Craig Ringer wrote:
Seriously, if you're facing DB corruption then something is already
horribly wrong with your setup.
True, but. In a past life, complaints from the db (it was a db that stored a
checksum with every
.
Was the server intentionally sent SIGUSR1 by an admin? Do you know what
triggered the signal?
Are you running any procedural languages other than PL/PgSQL, or any
custom C extensions? Anything that might have unwittingly cleared the
signal handler for SIGUSR1?
--
Craig Ringer
--
Sent via
On 10/22/2012 08:52 PM, Tom Lane wrote:
Craig Ringer ring...@ringerc.id.au writes:
On 10/19/2012 04:40 PM, raghu ram wrote:
2012-10-19 12:26:46 IST [1338]: [18-1] user=,db= LOG: server process
(PID 15565) was terminated by signal 10
That's odd. SIGUSR1 (signal 10) shouldn't terminate
On 10/23/2012 01:03 PM, Craig Ringer wrote:
http://stackoverflow.com/questions/6403803/how-to-get-backtrace-function-line-number-on-solaris
Actually, that link doesn't apply to this problem, it's for getting a
stack trace programmatically:
Try:
http://publib.boulder.ibm.com/httpserv/ihsdiag
for the problem DB in the public schema? If so, can
you do a schema-only dump?
pg_dump -U sns84 -n public -f $dbname.dump $dbname
--
Craig Ringer
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
/windows/desktop/ms680600(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/ms680600%28v=vs.85%29.aspx
... but it's in Winternl.h so it's not guaranteed to exist / be
compatible between versions and can only be accessed via runtime dynamic
linking. Not ideal.
--
Craig Ringer
will need to do some application testing.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
in
the code. On the other side, Oracle doesn't support DML's in functions
or procedures.
Er, what? What exactly do you mean Oracle doesn't support DML's in ...
procedures?
I'm assuming the intention was to write DDL.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin
On 10/11/2012 07:12 AM, Walter Hurry wrote:
OK, I'll do your work for you. $200/hr.
This was a polite and reasonable question, there is no need to be rude.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http
behaviour while fixing code.
I can understand not wanting to undertake it at the same time as a
hardware change, but you should probably schedule it for sooner rather
than later and start testing on the new version once you're on the
updated OS HW.
--
Craig Ringer
--
Sent via pgsql-admin
with -Wl,-rpath so the paths to required
libraries were embedded into the extension library.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
and
nasty hardware they're on).
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
.
If possible, dump the old database using the *new* pg_dump. If you're
using the PgAdmin-III GUI, connecting to the old DB from the new PgAdmin
on the new computer should do the trick.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your
-warm the caches. Search for postgresql
prewarm. See, eg:
http://archives.postgresql.org/message-id/ca+tgmobrrrxco+t6gcqrw_djw+uf9zedwf9bejnu+rb5teb...@mail.gmail.com
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http
completely crazy. More info needed, though.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
if there will be a performance drawback.
It still won't work. If you want to replicate from a 64-bit machine to a
32-bit machine you will need to build a 32-bit version of PostgreSQL on
the 64-bit machine.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes
-9.1-4.noarch.rpm
with the local file, or the URL; yum doesn't care.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
all) in future.
--
Craig Ringer
current_query = 'IDLE in transaction'
AND query_start current_timestamp - INTERVAL '3 seconds';
do it?
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On 08/10/2012 12:20 PM, Tom Lane wrote:
Craig Ringer ring...@ringerc.id.au writes:
On 08/10/2012 10:06 AM, Tom Lane wrote:
That sure sounds like the source of your problem. It might be sane if
it killed only processes that *had been idle* for at least three
seconds, but I'm not sure
to get the
data out of PostgreSQL.
--
Craig Ringer
or Talend to connect to both databases and merge/convert data.
--
Craig Ringer
usability wart - causing real-world
performance and reliability problems - that people unwittingly raise
max_connections to absurd levels because they get no warnings, hints or
guidance of any sort.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes
failing, and if that reproduces the fault you can use PgBench with the
other tests.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
sane. It doesn't do any kind of consistency checking between
index and table.
--
Craig Ringer
in
PostgreSQL's partitioning support.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
IS DISTINCT FROM NULL is
false. Very handy.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
.html
Consider updating. You don't need to do a dump and reload or use
pg_upgrade, since it's only a minor version update. Stop the DB, install
the new binaries, start the DB.
However, I don't see any fixes related to the stats writer in the
relnotes from the 9.0 series.
--
Craig Ringer
frequently create and drop tables, indexes, etc? Say, using
a database unit testing framework?
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
question, the more likely you will get useful help.
--
Craig Ringer
On 07/26/2012 04:39 PM, Thomas Markus wrote:
Hi,
see below
Am 26.07.2012 10:25, schrieb Craig Ringer:
- Do you have any uncommitted two phase transactions? Run:
SELECT * from pg_prepared_xacts ;
hm yes, i stopped all applications this morning but this query shows:
transaction | gid
TOAST table in the
repository schema, grows out of control !
Has somebody disabled autovacuum or set it to barely run at all?
Try setting autovacuum to very aggressively vacuum the problem table(s).
--
Craig Ringer
as
it seems to be a Windows issue.
--
Craig Ringer
is OK, anything else isn't) then remove or disable the firewall
and see if the problem goes away.
Otherwise, more info please. PostgreSQL version, OS and version, client
program, whether the client program is using psqlODBC, pgJDBC, libpq
directly, etc.
--
Craig Ringer
On 07/05/2012 02:30 AM, Akash Kodibail wrote:
-Staging table population happens in expected time. Anywhere from
10-15 minutes for each process.
-Aggregation process almost never completes which is a set of 15
insert queries. Entire process happens in a single commit (population
of staging
a bulk insert or update during their data load.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
autovaccum is turned off, so that's not surprising.
If you drop and recreate, or TRUNCATE, the tables between load runs you
might be OK with autovac off for those tables, but what you're
describing makes me think otherwise.
--
Craig Ringer
likely get much better
performance with max connections at 20 or less - the usual rule of thumb
is num_hdds + num_cpus but in reality you need to benchmark and tune
to work out what's best.
pgbouncer is a good light-weight pooling option.
--
Craig Ringer
about the load the server was on,
about what messages (if any) appear in the logs during the period when
it was refusing connections and leading up to it, the error message with
which it was refusing connections, etc.
--
Craig Ringer
. Anyone who's been responsible
for a database should ideally know better than to assume that
*transation* logs are disposable, but everyone has to learn sometime,
and not everybody does so by reading TFM (unfortunately).
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin
to?
After the save, if you examine the data in the database using psql or
PgAdmin-III, what do you see?
What PostgreSQL version are you using?
--
Craig Ringer
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
--
Craig Ringer
you about the error.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On 06/16/2012 08:36 PM, Christian Ullrich wrote:
* Peter Cheung wrote:
I’m new to PostgreSQL. I installed PostgreSQL on a Windows Server
2008
R2 server. I have created a database and an user in Windows Active
Directory. How can I configure that user to access that database?
The
. I haven't used
it myself. The user must still be created in PostgreSQL, SSPI just takes
care of authenticating them using their Windows credentials. See:
http://www.postgresql.org/docs/9.1/static/auth-methods.html
--
Craig Ringer
of doing this?
I think you'll want Bucardo or Slony-I for this. You certainly can't do
it with the built-in replication.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
that you may need to change the priority of the *backend* that
pg_basebackup is using, not necessarily the pg_basebackup command its
self. I haven't done enough with Pg's replication to know how that
works, so someone else will have to fill that bit in.
--
Craig Ringer
--
Sent via pgsql-admin
://wiki.postgresql.org/wiki/Corruption
If you did, it might be possible to tell what happened. If you didn't
then you've probably destroyed the evidence needed to determine what
went wrong (and maybe recover some lost data).
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin
?
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
any circular references.
--
Craig Ringer
and log
output, etc. Please don't be too hard on them.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
your first copy read-only. Duplicate the copy and
work on the duplicate when trying to restore. I'd start with enabling
zero_damaged_pages to see if you can get a dump that way.
Do **NOT** enable zero_damaged_pages on the original. Do it on the
duplicate of the copied data.
--
Craig Ringer
add up if someone was using automatic savepoints in (say) a 10,000
INSERT transaction.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
then anything could
happen.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
consider hiring a consultant
who knows PostgreSQL's innards and the table format well. See:
http://www.postgresql.org/support/professional_support/ .
--
Craig Ringer
' backend, but all the
setup applies just as well to pg_dump. You then start pg_dump via
windbg.exe or Visual Studio Express and, when it crashes, follow the
instructions given in the wiki to produce the backtrace.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin
://www.postgresql.org/docs/current/static/warm-standby.html
--
Craig Ringer
right.
--
Craig Ringer
dependencies but they'll still get used via LoadLibrary if
your database calls for them.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
bad - and in the latter case, who says it's
the catalog that's correct?
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
first? I
don't see any point vacuuming just to drop it, but this may be too much
of a corner case to justify the testing a special case like this would
require.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http
dump.
Do you know what caused this? The missing files suggest it was probably
file system corruption - was there a disk failure? fsck run with errors?
Unexpected reboot on a RAID controller with a dead backup battery?
--
Craig Ringer
) exited with exit
code 1
2011-11-09 16:25:04 MSK LOG: aborting startup due to startup process failure
I don't know enough about Pg's guts to suggest how to proceed from here.
Maybe a pg_resetxlog might get you up and running (albeit with potential
data damage) but I'm not sure.
--
Craig Ringer
semi-random reads
of the table.
Additionally, not all indexes can be used for all operations. For
example, a LIKE query with a prefix wildcard eg %FRED cannot use a
btree index, so any btree index on the searched field will be ignored.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql
it difficult or impossible to answer your question,
wasting your time and everyone else's.
We don't link to that document just for fun. It's information that is
important to answer questions properly.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes
should I do in this case?
http://wiki.postgresql.org/wiki/Troubleshooting_Installation
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
when you're chrooted into
another FS.
Also, you may have firewall rules in place that prevent the connection,
check for that.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
learned about using the documentation
and Google.
4. Storage Management
Details?
What do you want to know? Storage management is kind of a broad
category. You probably want to read the manual first, then look into
things like tablespaces, TOAST, vacuum and autovacuum, etc.
--
Craig Ringer
directly into PostgreSQL's internal
user list, as it's a different hash algorithm. You should still be able
to use other auth methods like PAM to use them, though.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http
inserts again.
That should help, but it's a form of trading timeliness off against
performance. Queries within the same transaction won't see the updated
`dataset' values, so if you're relying on them for correct operation
later in the transaction you could have issues.
--
Craig Ringer
--
Sent via
that the planner will avoid using it where it has any
alternative. In this case, it doesn't seem to think it has any other way
to execute the query, or it thinks that any other way will be so
incredibly, insanely slow that the merge join is still better.
--
Craig Ringer
--
Sent via pgsql-admin
was much faster. but, what
does link mean here? symbolic link? i saw that after the procedure the old
'data' folder was there too, and i could delete it, so i guess the answer is
not symbolic link. so what does that link mean? thx in advance
http://en.wikipedia.org/wiki/Hard_link
--
Craig Ringer
characters that exist in the target encoding.
You can't change the encoding of a database in-place.
--
Craig Ringer
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
or definitions. You'll note that function sources are
also available via pg_catalog, though it seems to be reasonably safe
(from what I hear, having not tested it) to change permissions to deny
access to those.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
that if something makes the backup fail,
you will be notified and the message will contain the error output from
the failed command.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
://www.postgresql.org/docs/8.2/interactive/release.html
I wouldn't particularly recommend installing such an old version on
64-bit Windows, either. It should work, but it won't be as well tested
and neither will the installer. Use a recent version if possible, or
just use 32-bit Windows.
--
Craig Ringer
to start it under your own user
account instead, but you haven't shown the full command line(s) involved.
--
Craig Ringer
in detail how
Postgres behaves under index corruption.
Like most forms of fault behaviour, it is undefined in the C standard
sense of the meaning, though perhaps not quite as willing to just eat
data ;-)
--
Craig Ringer
corruption.
--
Craig Ringer
socket).
Personally I wish Pg would permit the client to use md5 auth when ident
fails - support something like ident_or_md5 as an authmode. That'd
solve a lot of usability issues for new admins around configuring auth.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin
is most appreciated!
http://www.specialisedtools.co.uk
What's the standard SQL system?
What problem are you having that you want to solve?
Try explaining in more detail. What are you trying to achieve? What is
the current state of your effort? What have you already tried?
--
Craig Ringer
search include CloverETL
(http://www.cloveretl.com/) and Aptar (http://apatar.com/). I'm sure
there are tons more, but who knows what they're like.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
? You're missing a
lot of bug fixes.
--
Craig Ringer
, and there's always the
roll-your-own queue-based trigger replication system option. Of course,
both options would probably cost more than buying EDB's already built
and tested version...
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your
ancient, you're missing a
lot of bug fixes, and some of them DID relate to data durability issues.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
memory that causes libpq
to crash later, corrupting the stack, returning a bad pointer from a
function call, library headers not matching linked library sizes so
returned struct sizes/offsets are wrong, and lots lots more. It's not
simple.
--
Craig Ringer
--
Sent via pgsql-admin mailing list
need more information, please read this so you provide
enough detail to get a proper answer before following up:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
... since you didn't even mention what operating system you were running
on in your question!
--
Craig Ringer
--
Sent
your needs?
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
DLLs with the
same name aren't quite compatible. Yay!
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
with
--enable-debug, running make install and re-testing might get a better
backtrace - or might be similarly useless.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
specific and doesn't seem to be defined anywhere else.
What _application_ _level_ impact does this have for you? What changes
do your apps expect to see in their use of or communication with the
database?
I strongly suggest that you _test_ this in Pg and see.
--
Craig Ringer
--
Sent via pgsql
1 - 100 of 102 matches
Mail list logo
