|
|
pg_dump question
By Carol Walter at Dec 7, 2007, 8:36 pm UTC
Hello- I'm trying to do an upgrade of version 8.1 to 8.2.3. The restore is failing because of some code that is in some views. If I dump the just the schema and then dump just the data, will the views be included? Carol More...
Hello-
I'm trying to do an upgrade of version 8.1 to 8.2.3. The restore is
failing because of some code that is in some views. If I dump the
just the schema and then dump just the data, will the views be included?
Carol
1 Reply
|
|
|
lost data, possible playback?
By Palle Girgensohn at Dec 6, 2007, 5:51 pm UTC
Hi! A user accidentally deleted some results (i.e. tuples in a postgresql database) created this morning. I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab out 3 o'clock this morning. The results where created from about nine o'clock until noon, when the user accidentally... More...
Hi!
A user accidentally deleted some results (i.e. tuples in a postgresql
database) created this morning.
I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab out
3 o'clock this morning.
The results where created from about nine o'clock until noon, when the user
accidentally removed the results.
Would it be possible to replay the WAL logs and get the results from
pg_xlog + the dump? Or from a file system snapshot as of now (five pm) +
pg_xlog?
Regards,
Palle
6 Replies
|
|
|
Enhancement request
By Campbell, Lance at Dec 5, 2007, 4:38 pm UTC
This is a multi-part message in MIME format. ------_=_NextPart_001_01C83389.C1A986D4 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Could you please add to your to do list a schema parameter for vacuum? Example: VACUUM SCHEMA xyz; PostgreSQL would get a... More...
This is a multi-part message in MIME format.
------_=_NextPart_001_01C83389.C1A986D4
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Could you please add to your to do list a schema parameter for vacuum?
Example:
VACUUM SCHEMA xyz;
PostgreSQL would get a list of all of the tables found in the schema.
It would then loop through vacuuming each table in the schema.
I found today that I did not have max_fsm_pages set high enough in order
to vacuum the entire database. I vacuum daily to avoid problems. After
realizing that I cannot vacuum the entire database now, I then had to do
it at the table level. I had to go through all 13 of my schemas and
list each table I found into an SQL vacuum script. This was a real
waist of time. I will eventually restart PostgreSQL with an increased
value for max_fsm_pages so this will not be an issue.
But until then I will have to run a new script. I created a script with
420 SQL vacuum statements at the table level. I would have preferred to
create 13 vacuum SQL statements at the schema level.
Thanks for considering this enhancement,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
------_=_NextPart_001_01C83389.C1A986D4
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Could you please add to your to do list a schema parameter
for vacuum?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Example:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>VACUUM SCHEMA xyz;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>PostgreSQL would get a list of all of the tables found in
the schema. It would then loop through vacuuming each table in the schema.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I found today that I did not have max_fsm_pages set high
enough in order to vacuum the entire database. I vacuum daily to avoid
problems. After realizing that I cannot vacuum the entire database now, I
then had to do it at the table level. I had to go through all 13 of my
schemas and list each table I found into an SQL vacuum script. This was a
real waist of time. I will eventually restart PostgreSQL with an increased
value for max_fsm_pages so this will not be an issue. <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>But until then I will have to run a new script. I
created a script with 420 SQL vacuum statements at the table level. I
would have preferred to create 13 vacuum SQL statements at the schema level.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks for considering this enhancement, <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Lance Campbell</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Project Manager/Software Architect</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Web Services at Public Affairs</span></font><o:p></o:p></p>
<p class=MsoNormal><st1:place w:st="on"><st1:PlaceType w:st="on"><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>University</span></font></st1:PlaceType><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> of <st1:PlaceName
w:st="on">Illinois</st1:PlaceName></span></font></st1:place><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>217.333.0382</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>http://webservices.uiuc.edu</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C83389.C1A986D4--
23 Replies
|
|
|
I want to Collect information about postgresql
By ebubekir temizkan at Dec 5, 2007, 2:33 pm UTC
Hi. We want to perform a project and We will use rdbms in project.I want to learn some topics about postgresql .Firstly, is the postgresql database server free. For example . is there any licence and How much is support cost.Technically is the postgresql useful to develop a simualtion.Iappreciate... More...
Hi. We want to perform a project and We will use rdbms in project.I want to
learn some topics about postgresql .Firstly, is the postgresql database
server free. For example . is there any licence and How much is support
cost.Technically is the postgresql useful to develop a
simualtion.Iappreciate if you reply this mail as soon as possible.
Thanks for answers and helps.
2 Replies
|
|
|
grants and functions
By Remco Post at Dec 5, 2007, 11:57 am UTC
Hi All, I've been trying to figure this out and reading this list on this subject. I have a view that shows a user a lot of intresting info. Now, on a normal view, that does not call any functions, I can give my user select rights and he/she can see all the info, without me having to to grant any... More...
Hi All,
I've been trying to figure this out and reading this list on this subject.
I have a view that shows a user a lot of intresting info. Now, on a
normal view, that does not call any functions, I can give my user select
rights and he/she can see all the info, without me having to to grant
any rights on underlying tables.
The PUBLIC also has execute rights on all functions I've defined by
default, so far so good. But now, If I grant a USER SELECT on a VIEW
that uses a pl/pgsql function, for some reason, The normal inheritance
of permissions is lost as I've become used to.
Now I could define my FUNCTION with SECURITY DEFINER, which will
probably work around the problem, since this will give the USER database
owner privileges. Also, I could give the USER select rights on the
underlying tables, but there was a reason I only wanted him/her to see
the database via the VIEW I created, and there are a lot of tables involved.
Has anybody found a way around this? Is this a bug, a documented
shortcoming, or just a feature of PostgreSQL (at version 8.1)?
--
Met vriendelijke groeten,
Remco Post
SARA - Reken- en Netwerkdiensten http://www.sara.nl
High Performance Computing Tel. +31 20 592 3000 Fax. +31 20 668 3167
PGP Key fingerprint = 6367 DFE9 5CBC 0737 7D16 B3F6 048A 02BF DC93 94EC
"I really didn't foresee the Internet. But then, neither did the
computer industry. Not that that tells us very much of course - the
computer industry didn't even foresee that the century was going to
end." -- Douglas Adams0 Replies
|
|
|
Cannot kill autovacuum
By Aldor at Dec 4, 2007, 11:01 pm UTC
When I set up a database with tons of data I forgot to check in the .conf file that autovacuum was on. Now it seems that the stat collection has prepared some tasks to be done for autovacuum - from my experience I can tell that this will take weeks or even months. I disabled autovacuum in the conf... More...
When I set up a database with tons of data I forgot to check in the
.conf file that autovacuum was on.
Now it seems that the stat collection has prepared some tasks to be
done for autovacuum - from my experience I can tell that this will
take weeks or even months.
I disabled autovacuum in the conf file, shut down the database,
restarted it but the autovacuum process is comming up again and
again. If I kill the process it takes just some minutes to start
again to do it's jobs it has in memory.
So, my question should be quite simple - how do I shut down
autovacuum finally to not do anything more on this tables - I hope
the answer is also as simple, but until right now I didn't find any
solution for that.
I'm using 8.2.5 version.
3 Replies
|
|
|
PANIC: right sibling
By Scott Whitney at Dec 4, 2007, 6:05 pm UTC
I don't really understand the postings I've found on this issue. This has been going on for quite some time, but now that I'm regularly vacuuming this db, I was looking for any info on how to fix this problem...I don't even know enough about it to know what to include in this message. This is the... More...
I don't really understand the postings I've found on this issue. This has
been going on for quite some time, but now that I'm regularly vacuuming this
db, I was looking for any info on how to fix this problem...I don't even
know enough about it to know what to include in this message.
This is the last output of:
vacuumdb -f -z -v rt3
vacuumdb: vacuuming of database "rt3" failed: PANIC: right sibling is not
next child in "tickets1"
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2 Replies
|
|
|
WAL archiving hangs due to missing WAL segment
By Paul Schluck at Dec 4, 2007, 3:05 pm UTC
Hi, I recently had set up WAL archiving in a testing environment and all went well. As a test I let the disk fill up until archiving no longer was possible. Then a co-worker noticed this disk filling up and removed some WALL segments, that were not yet archived. Now the archiving proces hangs.... More...
Hi,
I recently had set up WAL archiving in a testing environment and all went
well. As a test I let the disk fill up until archiving no longer was
possible.
Then a co-worker noticed this disk filling up and removed some WALL
segments, that were not yet archived. Now the archiving proces hangs.
Messages in logfile:
2007-12-04 15:20:50 MET LOG: archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
failed: return code 256
2007-12-04 15:20:51 MET DEBUG: executing archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
cp: cannot access pg_xlog/00000001000000010000003A: No such file or
directory
2007-12-04 15:20:51 MET LOG: archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
failed: return code 256
2007-12-04 15:20:51 MET WARNING: transaction log file
"00000001000000010000003A" could not be archived: too many failures
How can I resolve this issue? I only have WALL segments from
000000010000000100000080 so I am missing all segments from
00000001000000010000003A upto 000000010000000100000080.
0 Replies
|
|
|
Is there an easy and safe way to migrate a database to a new tablespace?
By David Young at Dec 4, 2007, 11:16 am UTC
Hi, I recently converted to using a postgresql engine that support tablespaces. There's a database that I would like to move to a new tablespace for performance reasons. What is the safe and correct way of doing this? The documentation is a bit sketchy on this. Thanks in advance, David More...
Hi,
I recently converted to using a postgresql engine that support tablespaces.
There's a database that I would like to move to a new tablespace for
performance reasons. What is the safe and correct way of doing this? The
documentation is a bit sketchy on this.
Thanks in advance,
David
2 Replies
|
|
|
Re: Replication advice Many-to-one Slony
By Aldor at Dec 4, 2007, 11:08 am UTC
Hello Walfred, yes you can use slony, we use slony for huge data replication. One solution of them is the backup of many masters to one slave. It is in real time - and we're quite happy with the newest version of slony. Don't forget to not only add tables to your replication but to also add... More...
Hello Walfred,
yes you can use slony, we use slony for huge data replication. One
solution of them is the backup of many masters to one slave.
It is in real time - and we're quite happy with the newest version
of slony.
Don't forget to not only add tables to your replication but to also
add sequences additionally, because you may backup the tables and
content of them but when trying to use your backup you could have a
big nightmare when you cannot find the state of your sequences - so
your data is not lost but not instantly usable.
When you think of using a hot-copy system so that you just can
switch from the master when it brakes to your slave system I would
suggest you to think about the load that could go to the
backup-slave which should now be used as master, in this case we
calculate 1/3 of master-load to one slave, but that depends on the
application and the load of your databases.
Switching to Slave and back to master when it's again ready (also
with automatic replication back to the master) works great in slony.
If you have any further questions or need any help on setting up the
slony scripts just contact me directly.
Regards,
Aldor
Walfred Tedeschi wrote:
> Hi all,
>
> We intend to establish a central back-up for our system. It can be though as
> many machines (masters) linked to one Slave. The communication may be one
> directional (master -> slave) and would appreciate to have it almost in realtime.
> The data flux on the system is not very high in terms of transactions and
> volume of data. And the primary keys are planned in a way that there is no
> overlap over the masters (every master has its own range of PK) without
> overlapping. We were thinking about slony1.
>
> Since we are not sure about the decision, we would appreciate very much if you
> could advise us what to use and where to start.
>
> Thank you very much,
>
> Fred
>
>
> --
> Open WebMail Project (http://openwebmail.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
> message can get through to the mailing list cleanly
> 0 Replies
|
|
|
How to identify the current WAL segment file
By Tommy Cheng at Dec 3, 2007, 10:21 am UTC
Hi everyone, I am new to postgresql. So point me to somewhere else if you got the right direction. i am using PostgreSQL 8.1.9 on a linux system and i want to setup PITR. i found that the segment size is too large that the copying frequency is not high enough. So, i want to set up a cron job that... More...
Hi everyone,
I am new to postgresql. So point me to somewhere else if you got the right
direction.
i am using PostgreSQL 8.1.9 on a linux system and i want to setup PITR. i
found that the segment size is too large that the copying frequency is not
high enough.
So, i want to set up a cron job that periodically identifies the current WAL
segment file once a minute and scp it to other server.
How to do that, can someone give me a template script?
Btw, i saw archieve_timeout setting in
http://www.postgresql.org/docs/8.2/static/continuous-archiving.html
that may suits my need, can i use it in 8.1.9?
0 Replies
|
|
|
Character Corruption / Encoding Question
By Bill Willits at Nov 30, 2007, 10:05 pm UTC
This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C83349.9B1939D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have just upgraded our phppgadmin client from v3.5 to v.4.1.3. Now, = when we view special characters in phppgadmin... More...
This is a multi-part message in MIME format.
------=_NextPart_000_0023_01C83349.9B1939D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We have just upgraded our phppgadmin client from v3.5 to v.4.1.3. Now, when we view special characters in phppgadmin they are displayed incorrectly. If we update the character field (to a special character), it will not store properly. Our database encoding is SQL_ASCII. The same operations work fine in v.3.5 of phppgadmin and with pgadmin III.
Obviously, this is a phppgadmin issue (and I have posted a question to them), but I am just curious what could have changed to cause it, and what if any workaround there would be outside of reverting to old version of phppgadmin or getting a patch.
Thanks
Bill Willits
------=_NextPart_000_0023_01C83349.9B1939D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16544" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>We have just upgraded our phppgadmin client from
v3.5 to v.4.1.3. Now, when we view special characters in phppgadmin they
are displayed incorrectly. If we update the character field (to a special
character), it will not store properly. Our database encoding is
SQL_ASCII. The same operations work fine in v.3.5 of phppgadmin and with
pgadmin III. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Obviously, this is a phppgadmin issue (and I have
posted a question to them), but I am just curious what could have changed to
cause it, and what if any workaround there would be outside of reverting to old
version of phppgadmin or getting a patch.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thanks</FONT></DIV>
<DIV><FONT face=Arial size=2>Bill Willits</FONT></DIV></BODY></HTML>
------=_NextPart_000_0023_01C83349.9B1939D0--
1 Reply
|
|
|
connection limit exceeded
By Tena Sakai at Nov 30, 2007, 9:28 pm UTC
This is a multi-part message in MIME format. ------_=_NextPart_001_01C8338B.018C1F7B Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Everybody, psql: FATAL: connection limit exceeded for non-superusers A few questions come to mind... 1) Is this to do... More...
This is a multi-part message in MIME format.
------_=_NextPart_001_01C8338B.018C1F7B
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Everybody,
On my postgres 8.1.1, I get a response
psql: FATAL: connection limit exceeded for non-superusers
A few questions come to mind...
1) Is this to do with max_connections setting in the postgres
configuration file? (my setting is 100 (I think this is the
default).)
2) how would I look at the "open/used" connections?
3) should I try restarting postgres without changing the setting?
4) what repercussion would there be if I increase this number to,
say, 200? (and would that be a good idea?)
5) finally, what's the best fix? (Do I need to go to pgpool?)
Many thanks in advance.
Regards,
Tena Sakai
[email protected: t...@gallo.ucsf.edu]
------_=_NextPart_001_01C8338B.018C1F7B
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7651.59">
<TITLE>connection limit exceeded</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Hi Everybody,<BR>
<BR>
On my postgres 8.1.1, I get a response<BR>
psql: FATAL: connection limit exceeded for non-superusers<BR>
<BR>
A few questions come to mind...<BR>
<BR>
1) Is this to do with max_connections setting in the postgres<BR>
configuration file? (my setting is 100 (I think this is the<BR>
default).)<BR>
2) how would I look at the "open/used" connections?<BR>
3) should I try restarting postgres without changing the setting?<BR>
4) what repercussion would there be if I increase this number to,<BR>
say, 200? (and would that be a good idea?)<BR>
5) finally, what's the best fix? (Do I need to go to pgpool?)<BR>
<BR>
Many thanks in advance.<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C8338B.018C1F7B--
7 Replies
|
|
|
Converting from SQL_ASCII to UTF8
By Peter Koczan at Nov 29, 2007, 10:14 pm UTC
Hi all, I'd like to move my database encoding from SQL_ASCII to UTF8, mostly because "No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding." (from... More...
Hi all,
I'd like to move my database encoding from SQL_ASCII to UTF8, mostly
because "No encoding conversion will be done when the setting is
SQL_ASCII. Thus, this setting is not so much a declaration that a
specific encoding is in use, as a declaration of ignorance about the
encoding." (from
http://www.postgresql.org/docs/current/static/multibyte.html) I saw a
few threads on the list regarding this before, for instance this one
(http://archives.postgresql.org/pgsql-admin/2004-01/msg00225.php) but
there's a specific issue that I'm having that wasn't addressed.
I have some UTF-8 data in my databases, and it's causing dump/restore
to fail. Specifically, I'm seeing messages like:
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence
for encoding "UTF8": 0xe14c65
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT: COPY applicants, line 282
Which happens even if I specify "-E UTF8" in the pg_dump command.
Here's the weirder part. If I just update the encoding by hand in
pg_database (as cautiously suggested by Tom Lane in the aforementioned
thread), it works. I doubt this will work in the general case, and I'd
like to at least offer this option for other people's databases.
I also tried using GNU recode (version 3.6) as suggested in similar
threads, but I got errors in both the plain and custom pg_dump
formats.
$ recode ascii..utf8 man.sql
recode: man.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8'
$ recode ..utf8 man.sql
recode: man.sql failed: Invalid input in step `CHAR..UTF-8'
Any ideas?
Peter
1 Reply
|
|
|
clear statistics in pg_stat
By Campbell, Lance at Nov 29, 2007, 9:52 pm UTC
This is a multi-part message in MIME format. ------_=_NextPart_001_01C832CF.69887A0E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable How can I clear pg_stat views? I thought there was a command I could do. I added a series of indexes. I would like to start... More...
This is a multi-part message in MIME format.
------_=_NextPart_001_01C832CF.69887A0E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
How can I clear pg_stat views? I thought there was a command I could
do. I added a series of indexes. I would like to start out fresh with
the stats. I ran vacuum full analyze.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
------_=_NextPart_001_01C832CF.69887A0E
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>How can I clear pg_stat views? I thought there was a
command I could do. I added a series of indexes. I would like to start out
fresh with the stats. I ran vacuum full analyze.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Lance Campbell</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Project Manager/Software Architect</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Web Services at Public Affairs</span></font><o:p></o:p></p>
<p class=MsoNormal><st1:place w:st="on"><st1:PlaceType w:st="on"><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>University</span></font></st1:PlaceType><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> of <st1:PlaceName
w:st="on">Illinois</st1:PlaceName></span></font></st1:place><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>217.333.0382</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>http://webservices.uiuc.edu</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C832CF.69887A0E--
2 Replies
|
|
|
Enable Syslog on Postgres
By Gaurav (Gaurav) Bansal at Nov 29, 2007, 5:18 pm UTC
This is a multi-part message in MIME format. ------_=_NextPart_001_01C831AD.36982E0E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I have installed Postgres 8.2.5 on windows xp machine using the windows installer. I am trying to enable syslogs on... More...
This is a multi-part message in MIME format.
------_=_NextPart_001_01C831AD.36982E0E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
I have installed Postgres 8.2.5 on windows xp machine using the windows
installer. I am trying to enable syslogs on Postgres so that all
Postgress logs should be forwarded Syslog server. I have installed
Syslog server WinSyslog on the same machine.
I have modified 'log_destination' parameter value in postgresql.conf to
'syslog'. However, the database server fails to start and print
following error message:
FATAL: invalid value for parameter "log_destination": "syslog"
Also, I want to know how can I forward logs to a syslog server installed
on a machine different from database server's machine.
Any help/ pointer is much appreciated.
Regards
Gaurav
------_=_NextPart_001_01C831AD.36982E0E
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.3199" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2>Hi,</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080 size=2>I
have installed Postgres 8.2.5 on windows xp machine using the windows
installer. I am trying to enable syslogs on Postgres so that all Postgress logs
should be forwarded Syslog server. I have installed Syslog server WinSyslog
on the same machine. </FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080 size=2>I
have modified 'log_destination' parameter value in postgresql.conf to 'syslog'.
However, the database server fails to start and print following error
message:</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2>FATAL: invalid value for parameter "log_destination":
"syslog"</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2>Also, I want to know how can I forward logs to a syslog server installed
on a machine different from database server's machine.</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080 size=2>Any
help/ pointer is much appreciated.</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2>Regards</FONT></SPAN></DIV>
<DIV><SPAN class=340424910-28112007><FONT face=Verdana color=#000080
size=2>Gaurav</FONT></SPAN></DIV>
<DIV><SPAN
style="FONT-SIZE: 10pt; COLOR: #999999; FONT-FAMILY: 'Century Gothic'"><FONT
face=Verdana color=#000080></FONT> </DIV><?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN>
<DIV> </DIV></BODY></HTML>
------_=_NextPart_001_01C831AD.36982E0E--
2 Replies
|
|
|
Why dos select work when pg_dump fails?
By Darren Reed at Nov 29, 2007, 3:20 pm UTC
For whatever reason, I'm back dealing with corrupted tables but curiously while this fails: pg_dump -U postgres -a -t table -f /data/table.dump this works: psql -U postgres -A -o /data/table.dump -c "SELECT * FROM table;" Graned the format of the data is different, but regardless, the data is still... More...
For whatever reason, I'm back dealing with corrupted tables but
curiously while this fails:
pg_dump -U postgres -a -t table -f /data/table.dump
this works:
psql -U postgres -A -o /data/table.dump -c "SELECT * FROM table;"
Graned the format of the data is different, but regardless, the data is
still there.
The message from pg_dump when it fails is:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: out of memory
DETAIL: Failed on request of size 20.
pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as
indexname, pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef,
t.relnatts as indnkeys, i.indkey, i.indisclustered, c.contype,
c.conname, c.tableoid as contableoid, c.oid as conoid, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as
tablespace FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON
(t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_depend d ON (d.classid =
t.tableoid AND d.objid = t.oid AND d.deptype = 'i') LEFT JOIN
pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid
= c.oid) WHERE i.indrelid = '16456'::pg_catalog.oid ORDER BY indexname
Darren
1 Reply
|
|
|
pg_restore problem
By Dinesh at Nov 29, 2007, 01:05 am UTC
Hi ALL, I am trying to restore 2G dump file using pg_restore and it will erros out with the following message; pg_restore: [custom archiver] could not uncompress data: too many length or distance symbol". Has anyone seen this before? I 'd really appreciate your help. Thanks, Dinesh More...
Hi ALL,
I am trying to restore 2G dump file using pg_restore and it will erros
out with the following message;
pg_restore: [custom archiver] could not uncompress data: too many length
or distance symbol". Has anyone seen this before? I 'd really appreciate
your help.
Thanks,
Dinesh
0 Replies
|
|
|
Case studies
By Elvis Henríquez at Nov 28, 2007, 10:32 am UTC
Hello everybody. Rather than asking for a technical detail or writing about a problem, I'm asking for up-to-date case studies involving PostgreSQL. The company where I'm actually working is migrating some apps; one requires a Data Warehouse, and I'm proposing PostgreSQL, but they're thinking of... More...
Hello everybody.
Rather than asking for a technical detail or writing about a problem,
I'm asking for up-to-date case studies involving PostgreSQL.
The company where I'm actually working is migrating some apps; one
requires a Data Warehouse, and I'm proposing PostgreSQL, but they're
thinking of Oracle, as the system has one table (among others) with 20
fields and more then 19 millions of records, and this exists for 2
subcompanies, which are actually in different databases, but the
migration project implies joining the two subcompanies data.
This amount of data is the result of 8 years of usage, and the data
growing rate has increased in the last two years.
They asked me for case studies that shows this, so they can go ahead
with the migration to PostgreSQL.
The ones shown in "http://www.postgresql.org/about/casestudies/" are
out-dated and don't show amount of records or database/table size or
growth.
I hope you could help me with this.
5 Replies
|
|
|
postmaster -D PGDATA and postmaster.pid
By Medi Montaseri at Nov 27, 2007, 10:17 pm UTC
Hi, I am configuring a PG 8.1.9 on CentOS 5. I am seeing some anomalies and would like you feedback. I have defined a new PGDATA=/qmsvol/pg_8.1.9/data, ran initdb -D /qmsvol/pg_8.1.9/data successfully and can start the engine with pg_ctl -D $PGDATA. I have also populated... More...
Hi,
I am configuring a PG 8.1.9 on CentOS 5. I am seeing some anomalies and
would like you feedback.
I have defined a new PGDATA=/qmsvol/pg_8.1.9/data, ran initdb -D
/qmsvol/pg_8.1.9/data successfully and can start the engine with pg_ctl -D
$PGDATA.
I have also populated /etc/sysconfig/pgsql/postgresql which overrides PGDATA
and PGLOG to my values.
However if I try to start the engine with "/etc/init.d/postgresql start " or
service postgresql start" I see the following error message in my PGLOG (ie
/var/log/pgsql/pgstartup.log)
postmaster cannot access the server configuration file
"/qmsvol/pg_8.1.9/data/postgresql.conf": Permission denied
ls -ld /qmsvol /qmsvol/pg_8.1.9 /qmsvol/pg_8.1.9/data
/qmsvol/pg_8.1.9/data/postgresql.conf
drwxrwxrwx 4 root root 4096 Nov 26 10:39 /qmsvol
drwx------ 3 postgres postgres 4096 Nov 26 11:25 /qmsvol/pg_8.1.9
drwx------ 11 postgres postgres 4096 Nov 26 12:04 /qmsvol/pg_8.1.9/data
-rw------- 1 postgres postgres 13699 Nov 26 11:25
/qmsvol/pg_8.1.9/data/postgresql.conf
Any ideas ?
Thanks
medi
2 Replies
|
|
 | |
