to just report
errors 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
discussed a long time 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:
o high-rate brute
forcing attempts but your IDS 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 yo
, measuring how much memory Pg uses is seriously frustrating because
OS accounting for shared memory is so bad.
See 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
of the PostgreSQL database while the server is stopped. See
http://wiki.postgresql.org/wiki/Corruption .
--
Craig Ringer
_value FROM %I.%I', nspname,
seqname) INTO seqval;
PERFORM setval(
quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 0);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Change "+ 0" to whatever your increment is.
--
Craig Ringer
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/
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
500.
Well, you're on an old version, but not one with any known serious
issues AFAIK.
--
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
ToDosError may also be of interest:
http://msdn.microsoft.com/en-us/library/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
ce;
in the problem DB?
What about \dn in psql?
Is all your data 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 (pgsq
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/htt
On 10/22/2012 08:52 PM, Tom Lane wrote:
> Craig Ringer 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 (
IGUSR1 (signal 10) shouldn't terminate PostgreSQL.
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
s
you 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
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
--
S
8.4 version of
the extension was built 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
want to preserve the old
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.
--
Crai
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:
me of the cheap 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
u want to do is "pre-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.o
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
wonder 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.
-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
to script with vbscript or whatever you prefer, using psql to get the
data out of PostgreSQL.
--
Craig Ringer
On 08/10/2012 12:20 PM, Tom Lane wrote:
Craig Ringer 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 there is any easy w
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
an see exactly what queries this "openquery" tool runs against PostgreSQL.
Please reply via the mailing list ("reply all") in future.
--
Craig Ringer
real 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 chang
ool like
Pentaho or Talend to connect to both databases and merge/convert data.
--
Craig Ringer
cks prove nothing about subtle corruption or
incorrect contents, they only make sure Pg can read them and they look
vaguely sane. It doesn't do any kind of consistency checking between
index and table.
--
Craig Ringer
tup that's currently
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
uldn't it be nice to fix" list 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
any other, so "NULL 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
static/release-9-0-8.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
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
question, the more likely you will get useful help.
--
Craig Ringer
prevent vacuum from cleaning things up.
Do you very 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
undle 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
matters as
it seems to be a Windows issue.
--
Craig Ringer
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
and indexes are probably bloating horribly because
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
icit ANALYZE or two after
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
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 tab
ou'll 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
e used to logs being disposable. 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
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
ntime-config-resource.html
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
--
Craig Ringer
ect 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
ror in your app, not Pg. Pg is just telling 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 one-cli
Pg wasn't built with GSSAPI and SSPI support. You
shouldn't have to do anything.
--
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
ctive Directory. 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
nice" commands to change I/O and CPU priority, respectively.
Note 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 some
ns problem. No filesystem or disk error.
Can you help me to know what happened?
Did you take a copy of the PostgreSQL data directory and error logs
before you tried to fix the problem, as per the advice here:
http://wiki.postgresql.org/wiki/Corruption
If you did, it might be possible
s also faster but there's not as much difference there.
If you have a need for stronger hashing functions you might want to
contact one of the consultants who does contract work on PostgreSQL
development and find out what'd be involved in funding the development
of the feature. Think about
have any circular references.
--
Craig Ringer
hine. Make 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.
t" question. They've made an effort, posted useful info 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
vial cost that would
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
e `postgres.exe' 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 maili
x27;t afford to risk losing
*any* possibly recoverable data, you should consider hiring a consultant
who knows PostgreSQL's innards and the table format well. See:
http://www.postgresql.org/support/professional_support/ .
--
Craig Ringer
up, or something similarly horrible 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
.html
http://www.postgresql.org/docs/current/static/warm-standby.html
--
Craig Ringer
that it's a lot more work to get right.
--
Craig Ringer
n't
see direct 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
ossible to drop a
database that's hit or nearly hit OID wraparound w/o a vacuum 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-
y arises when someone does something silly
or when a file system goes 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
information about your PostgreSQL version. Failure to provide that
information makes 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
n reading the index then doing 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
--
9 16:25:04 MSK CONTEXT: writing block 0 of relation
> base/16385/86064815_vm
> 2011-11-09 16:25:04 MSK LOG: startup process (PID 3570) 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
to set
zero_damaged_pages to get a usable 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
complete correctly.
>
> What 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
ect because
Pg isn't listening on your public IP, only your loopback IP. A chroot
won't affect tcp/ip, so it's still localhost when you're chrooted into
another FS.
Also, you may have firewall rules in place that prevent the connection,
check for that.
--
Craig Ringer
sting `crypt'ed passwords over 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.or
thing best 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, et
this will fix
our locking problem and hopefully speed up our 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
te so high 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
only 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
ink option 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_l
t limiting access to
_data_ not DDL 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
rror email
containing "Manually aborting" when it is there.
Once that's done, you'll know 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
http://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.
won't start as a service. It looks
like you used some kind of batch file to start it under your own user
account instead, but you haven't shown the full command line(s) involved.
--
Craig Ringer
an active issue. Just trying to understand 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
, and why you (presumably) suspect you
have index corruption.
--
Craig Ringer
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?
instead if your app uses a unix 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
--
ers found in a quick 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 subscript
ts of ETL tools available, 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
latest release is 8.3.15? You're missing a
lot of bug fixes.
--
Craig Ringer
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
, but it
might not land up telling me anything. Similarly, rebuilding Pg 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
esses like this where two 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
if PostgreSQL's behavior fits 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
still 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 Ringe
r it
might be that some other library is corrupting 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.
--
1 - 100 of 104 matches
Mail list logo
