Phoenix Kiula wrote:
Thanks Andrew.
On the server (the DB to be dumped) everything is "UTF8".
On my home server (where I would like to mirror the DB), this is the output:
=# \l
List of databases
Name| Owner | Encoding
---+-+---
pos
-- Original message --
From: "Phoenix Kiula" <[EMAIL PROTECTED]>
> On 8/30/08, Adrian Klaver <[EMAIL PROTECTED]> wrote:
> >
> > Might also be useful to do SHOW ALL from within the database cluster you
> > have
> > on your restore machine. Will show what choices in
On 8/30/08, Adrian Klaver <[EMAIL PROTECTED]> wrote:
>
> Might also be useful to do SHOW ALL from within the database cluster you have
> on your restore machine. Will show what choices initdb made.
>
> test=# SHOW all;
>
> lc_collate | en_US.UTF-8
> lc_ctype
On Friday 29 August 2008 8:29:55 am Phoenix Kiula wrote:
> Thanks so much! I think we may be on to something:
>
>
> On the pg_dump machine:
>
> LANG=en_US.UTF-8
> LC_CTYPE="en_US.UTF-8"
> LC_NUMERIC="en_US.UTF-8"
> LC_TIME="en_US.UTF-8"
> LC_COLLATE="en_US.UTF-8"
> LC_MONETARY="en_US.UTF-8"
> LC_ME
On Fri, Aug 29, 2008 at 11:29:55PM +0800, Phoenix Kiula wrote:
> So how can I change the locale on my local OSX machine? This could be
> it. Thanks!
Before you start restoring, put a LANG environment variable. So
something like
LANG=en_US.UTF-8 psql
or else
LANG=en_US.UTF-8; export LANG
[oth
Thanks so much! I think we may be on to something:
On the pg_dump machine:
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="
On Friday 29 August 2008 8:12:35 am Phoenix Kiula wrote:
> On 8/29/08, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> > Hrm. Well, what about the locale, as I suggested? I have no idea if
>
> How should I check for the locale?
On Linux:
[EMAIL PROTECTED]:~$ locale
LANG=en_US.UTF-8
LANGUAGE=en
LC_
On Fri, Aug 29, 2008 at 11:12:35PM +0800, Phoenix Kiula wrote:
>
> How should I check for the locale?
As the user that you issue the pg_dump with on the machine you
generated it, and as the user you're trying to use to restore the
data, at the command line type
$ locale
You end up with somethi
On 8/29/08, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>
>
> Hrm. Well, what about the locale, as I suggested? I have no idea if
How should I check for the locale?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql
On Fri, Aug 29, 2008 at 10:26:14PM +0800, Phoenix Kiula wrote:
> Thanks Andrew.
>
> On the server (the DB to be dumped) everything is "UTF8".
>
> On my home server (where I would like to mirror the DB), this is the output:
Hrm. Well, what about the locale, as I suggested? I have no idea if
it'
Thanks Andrew.
On the server (the DB to be dumped) everything is "UTF8".
On my home server (where I would like to mirror the DB), this is the output:
=# \l
List of databases
Name| Owner | Encoding
---+-+---
postgres | postgres
On Fri, Aug 29, 2008 at 01:53:28PM +0800, Phoenix Kiula wrote:
> My database encoding on BOTH the sides is UTF8. The dumped DB is UTF8.
Are you sure? I know people asked up-thread for the output of \l, but
I haven't seen it. If your encoding is UTF-8, then I don't understand
how you got non-UTF
On 8/29/08, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>
> The latter should be in effect if your database encoding is UTF-8.
> What's the database encoding?
My database encoding on BOTH the sides is UTF8. The dumped DB is UTF8.
The one I am importing into is also UTF8. So why is it showing me t
On Thu, Aug 28, 2008 at 07:51:57AM +0800, Phoenix Kiula wrote:
> system-provided way of checking for rows that may not be UTF8? Or to
> even have a constraint to this effect, to disallow any non-UTF8 data
> from getting in there?
The latter should be in effect if your database encoding is UTF-8.
Is there a mechanism to check exactly which row may have characters
that are not UTF8? I am trying with this kind of a command:
select id from employee
where modify_date between '2008-03-01' and '2008-07-01'
and joint_field_of_name_etc <> convert(joint_field_of_name_etc, 'UTF8')
This is of course
Hi,
Phoenix Kiula wrote:
Hello. I have a database dump. With data and schema, which includes
all the constraints and rules. But it seems the pg_dumpall command
does not copy data in such a way that foreign keys are satisfied upon
restoring. Because tables are inter-related, importing them keep
g
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> This is the dump command being used on a 8.2.3 database on Linux:
>$ pg_dumpall > mydb.sql
>$ umask 077
>$ gzip mydb.sql
> Then I download the mydb.sql.gz file into my local computer (Mac OSX
> with 8.3.3) and unzip it to mydb.sql.
That's
On Wed, Aug 27, 2008 at 8:40 AM, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> Thanks. But there seems to be a tangible slowdown of DB operations
> during the time that pg_dump is running. Perhaps some of my
> postgresql.conf variables are not geared to this. (I vaguely recall
> setting it up so that
On Wed, Aug 27, 2008 at 11:54:27PM +0800, Phoenix Kiula wrote:
> OLD DB:
> Since 2002. May contain non-UTF8 data. But I thought I had modified it
> all when I changed it to UTF-8 pgsql database (it was originally
> Mysql). The database works very well on a very busy website.
> Everything on that w
Ok. Here is what it is.
OLD DB:
Since 2002. May contain non-UTF8 data. But I thought I had modified it
all when I changed it to UTF-8 pgsql database (it was originally
Mysql). The database works very well on a very busy website.
Everything on that website is now UTF-8. I wish to mirror this
databa
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> On Wed, Aug 27, 2008 at 10:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> >
> > No, you were right the first time: just feed the dump script to psql.
> >
>
> Ok. Tried that. Because there's no database, I have to execute the
> psql command as
On Wed, Aug 27, 2008 at 11:20:55PM +0800, Phoenix Kiula wrote:
> This seems to be it. This is what I should be executing, except that
> it spews out many errors, like:
>
>ERROR: invalid byte sequence for encoding "UTF8": 0x80
You have bad data in your database. Apparently, you have an enco
On Wed, Aug 27, 2008 at 10:40:41PM +0800, Phoenix Kiula wrote:
> Thanks. But there seems to be a tangible slowdown of DB operations
> during the time that pg_dump is running.
Yes. Pg_dump copies all the data out, so it puts load on your
database and disks.
A
--
Andrew Sullivan
[EMAIL PROTECTE
On Wed, Aug 27, 2008 at 10:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> No, you were right the first time: just feed the dump script to psql.
>
Ok. Tried that. Because there's no database, I have to execute the
psql command as "postgres" otherwise it doesn't work.
So here's my command:
$
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> Then I tried this:
> $ pg_restore -h localhost mydb.sql
> This tells me:
> pg_restore: [archiver] input file does not appear to be a valid archive
No, you were right the first time: just feed the dump script to psql.
regar
On Wed, Aug 27, 2008 at 10:42 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> For pg_dumpall, you shouldn't even have created the
> databases, just start from a virgin installation.
Should I have CREATEd the db at least without the table structure?
I dropped the database locally. Entirely. Gone.
T
On Wed, Aug 27, 2008 at 10:34 PM, Bill Moran
<[EMAIL PROTECTED]> wrote:
>>
>>
>> Dumb question. Will this kind of pg_dumpall lead to downtime, I mean
>> is there a database lock during this time?
>
> No.
Thanks. But there seems to be a tangible slowdown of DB operations
during the time that pg_du
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> Thanks Bill for this recommendation:
>
>
> > *) In the early step dump the data only with pg_dumpall --disable-triggers
> > -a
>
>
> Dumb question. Will this kind of pg_dumpall lead to downtime, I mean
> is there a database lock during thi
Thanks Bill for this recommendation:
> *) In the early step dump the data only with pg_dumpall --disable-triggers -a
Dumb question. Will this kind of pg_dumpall lead to downtime, I mean
is there a database lock during this time?
Thanks!
--
Sent via pgsql-general mailing list (pgsql-general@p
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> > The recommended solution is to use a regular schema-and-data dump.
> > If you really have to separate schema and data, the --disable-triggers
> > switch might help, though you open yourself to the possibility of
> > loading inconsistent data
On Wednesday 27 August 2008 09:36, Phoenix Kiula wrote:
> On Wed, Aug 27, 2008 at 9:29 PM, Terry Lee Tucker <[EMAIL PROTECTED]>
wrote:
> > We have all sorts of constraints and foreign keys and we have never had
> > any problem with pg_restore related to dumping such that foreign keys are
> > satis
> The recommended solution is to use a regular schema-and-data dump.
> If you really have to separate schema and data, the --disable-triggers
> switch might help, though you open yourself to the possibility of
> loading inconsistent data that way.
Thanks Tom.
This is the dump command being used
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> Hello. I have a database dump. With data and schema, which includes
> all the constraints and rules. But it seems the pg_dumpall command
> does not copy data in such a way that foreign keys are satisfied upon
> restoring. Because tables are inter-relate
On Wed, Aug 27, 2008 at 9:29 PM, Terry Lee Tucker <[EMAIL PROTECTED]> wrote:
> We have all sorts of constraints and foreign keys and we have never had any
> problem with pg_restore related to dumping such that foreign keys are
> satisfied. You must have data already in the database that violates t
On Wednesday 27 August 2008 09:19, Phoenix Kiula wrote:
> Hello. I have a database dump. With data and schema, which includes
> all the constraints and rules. But it seems the pg_dumpall command
> does not copy data in such a way that foreign keys are satisfied upon
> restoring. Because tables are
Hello. I have a database dump. With data and schema, which includes
all the constraints and rules. But it seems the pg_dumpall command
does not copy data in such a way that foreign keys are satisfied upon
restoring. Because tables are inter-related, importing them keep
giving errors and eventually
36 matches
Mail list logo