Re: pg_dump to a remote server

[Adrian Klaver]

On 04/16/2018 06:43 PM, Ron wrote:



On 04/16/2018 07:18 PM, Adrian Klaver wrote:

On 04/16/2018 04:58 PM, Ron wrote:
We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The 
dump file will be more than 1TB, and there's not enough disk space on 
the current system for the dump file.


Thus, how can I send the pg_dump file directly to the new server 
while the pg_dump command is running?  NFS is one method, but are 
there others (netcat, rsync)?  Since it's within the same company, 
encryption is not required.


Maybe?:

pg_dump -d test -U postgres -Fc | ssh aklaver@arkansas 'cat > 
test_cat.out'


That looks promising.  I could then "pg_restore -jX".


More promising would be the suggestion from Michael Nolan:

https://www.postgresql.org/message-id/CAOzAqu%2BVpOfzBHwcqptSzm3PkeZAjkqqc0XqB%2BA-jBNioU6x%2Bg%40mail.gmail.com

"Can you run pg_dump on the new server, connecting remotely to the 
current one?"


It eliminates two programs(ssh and cat) and a pipe.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Keith Fiske]

On Mon, Apr 16, 2018 at 2:26 PM, Adrian Klaver 
wrote:

> On 04/16/2018 10:18 AM, Keith Fiske wrote:
>
>>
>>
>> On Mon, Apr 16, 2018 at 12:21 PM, Tom Lane  t...@sss.pgh.pa.us>> wrote:
>>
>>
>
>> So playing around with the "client_encoding" configuration option in
>> postgresql.conf. According to the docs, setting this should set the default
>> encoding for any client that connects, right?
>>
>> https://www.postgresql.org/docs/10/static/runtime-config-cli
>> ent.html#GUC-CLIENT-ENCODING > cs/10/static/runtime-config-client.html#GUC-CLIENT-ENCODING>
>>
>> That description also seems misleading in saying that the default client
>> encoding is what the database encoding is as well. At least as far as psql
>> is concerned, right? I've tried setting that value in postgresql.conf but
>> psql keeps setting it to UTF8 when I connect to the SQL_ASCII database
>> unless I then specifically run "set client_encoding = 'SQL_ASCII';"
>>
>
> Realized that while my previous suggestion of setting PGCLIENTENCODING to
> 'SQL_ASCII' would work it would also affect libpq programs beside psql.
>
> For a finer grained approach set \encoding 'SQL_ASCII' in a .psqlrc file.:
>
> https://www.postgresql.org/docs/10/static/app-psql.html
>
> "Files
>
> psqlrc and ~/.psqlrc
>
> Unless it is passed an -X option, psql attempts to read and execute
> commands from the system-wide startup file (psqlrc) and then the user's
> personal startup file (~/.psqlrc), after connecting to the database but
> before accepting normal commands. These files can be used to set up the
> client and/or the server to taste, typically with \set and SET commands.
>
> The system-wide startup file is named psqlrc and is sought in the
> installation's “system configuration” directory, which is most reliably
> identified by running pg_config --sysconfdir. By default this directory
> will be ../etc/ relative to the directory containing the PostgreSQL
> executables. The name of this directory can be set explicitly via the
> PGSYSCONFDIR environment variable.
>
> The user's personal startup file is named .psqlrc and is sought in the
> invoking user's home directory. On Windows, which lacks such a concept, the
> personal startup file is named %APPDATA%\postgresql\psqlrc.conf. The
> location of the user's startup file can be set explicitly via the PSQLRC
> environment variable.
>
> Both the system-wide startup file and the user's personal startup file
> can be made psql-version-specific by appending a dash and the PostgreSQL
> major or minor release number to the file name, for example ~/.psqlrc-9.2
> or ~/.psqlrc-9.2.5. The most specific version-matching file will be read in
> preference to a non-version-specific file.
>
> "
>
>
>> --
>> Keith Fiske
>> Senior Database Engineer
>> Crunchy Data - http://crunchydata.com
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Adrian,

Thanks for all the info! This will definitely help a lot getting the new
environment set up until we can start working on migrating off SQL_ASCII.

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: pg_dump to a remote server

[Ron]

On 04/16/2018 07:47 PM, Gao Jack wrote:

-Original Message-
From: Ron 
Sent: Tuesday, April 17, 2018 7:59 AM
To: pgsql-general 
Subject: pg_dump to a remote server

We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump
file will be more than 1TB, and there's not enough disk space on the current
system for the dump file.

Thus, how can I send the pg_dump file directly to the new server while the
pg_dump command is running?  NFS is one method, but are there others
(netcat, rsync)?  Since it's within the same company, encryption is not
required.

Or would it be better to install both 8.4 and 9.6 on the new server (can I
even install 8.4 on RHEL 6.9?), rsync the live database across and then set
up log shipping, and when it's time to cut over, do an in-place pg_upgrade?

(Because this is a batch system, we can apply the data input files to bring
the new database up to "equality" with the 8.4 production system.)

Thanks

--
Angular momentum makes the world go 'round.

Hi

https://www.postgresql.org/docs/current/static/backup-dump.html#BACKUP-DUMP-RESTORE

...
...

The ability of pg_dump and psql to write to or read from pipes makes it 
possible to dump a database directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname


But that assumes --format=plain which will send a whole lot of uncompressed 
text across the wire.


--
Angular momentum makes the world go 'round.

RE: pg_dump to a remote server

[Gao Jack]

> -Original Message-
> From: Ron 
> Sent: Tuesday, April 17, 2018 7:59 AM
> To: pgsql-general 
> Subject: pg_dump to a remote server
> 
> We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump
> file will be more than 1TB, and there's not enough disk space on the current
> system for the dump file.
> 
> Thus, how can I send the pg_dump file directly to the new server while the
> pg_dump command is running?  NFS is one method, but are there others
> (netcat, rsync)?  Since it's within the same company, encryption is not
> required.
> 
> Or would it be better to install both 8.4 and 9.6 on the new server (can I
> even install 8.4 on RHEL 6.9?), rsync the live database across and then set
> up log shipping, and when it's time to cut over, do an in-place pg_upgrade?
> 
> (Because this is a batch system, we can apply the data input files to bring
> the new database up to "equality" with the 8.4 production system.)
> 
> Thanks
> 
> --
> Angular momentum makes the world go 'round.

Hi

https://www.postgresql.org/docs/current/static/backup-dump.html#BACKUP-DUMP-RESTORE

...
...

The ability of pg_dump and psql to write to or read from pipes makes it 
possible to dump a database directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname


--
Jack Gao
jackg...@outlook.com

Re: pg_dump to a remote server

[Michael Nolan]

On Mon, Apr 16, 2018 at 6:58 PM, Ron  wrote:

> We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump
> file will be more than 1TB, and there's not enough disk space on the
> current system for the dump file.
>
> Thus, how can I send the pg_dump file directly to the new server while the
> pg_dump command is running?  NFS is one method, but are there others
> (netcat, rsync)?  Since it's within the same company, encryption is not
> required.
>

Can you run pg_dump on the new server, connecting remotely to the current
one?
--
Mike Nolan

Re: pg_dump to a remote server

[Adrian Klaver]

On 04/16/2018 04:58 PM, Ron wrote:
We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The 
dump file will be more than 1TB, and there's not enough disk space on 
the current system for the dump file.


Thus, how can I send the pg_dump file directly to the new server while 
the pg_dump command is running?  NFS is one method, but are there others 
(netcat, rsync)?  Since it's within the same company, encryption is not 
required.


Maybe?:

pg_dump -d test -U postgres -Fc | ssh aklaver@arkansas 'cat > test_cat.out'




Or would it be better to install both 8.4 and 9.6 on the new server (can 
I even install 8.4 on RHEL 6.9?), rsync the live database across and 
then set up log shipping, and when it's time to cut over, do an in-place 
pg_upgrade?


(Because this is a batch system, we can apply the data input files to 
bring the new database up to "equality" with the 8.4 production system.)


Thanks




--
Adrian Klaver
adrian.kla...@aklaver.com

To prefer sorts or filters in postgres, that is the question....

[Bob Jones]

Hi,

I've been playing around with hierarchical queries a bit and one thing
I wanted to do is build a query that gives the ultimate parent for a
given child.

The two queries below seem to be a good a shortlist as any.

I'm no query-plan guru, but these seem to be  largely identical aside
from one uses "filter IS NULL" and the other uses "top-N heapsort".

Would there be a reason to prefer one over the other (or perhaps
there's an altogether more efficient way of doing this query ?!?).
My gut-instinct says the sort version ?

=> explain analyze with recursive cte(cmenu_id,depth,cmenu_parent) as (
SELECT cmenu_id,1 as depth,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,cte.depth-1,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte order by depth LIMIT 1;

QUERY PLAN
---
 Limit  (cost=166.59..166.59 rows=1 width=68) (actual
time=0.132..0.132 rows=1 loops=1)
   CTE cte
 ->  Recursive Union  (cost=0.15..165.31 rows=51 width=68) (actual
time=0.023..0.070 rows=4 loops=1)
   ->  Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=68) (actual time=0.022..0.022 rows=1
loops=1)
 Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
 Filter: cmenu_active
   ->  Hash Join  (cost=0.33..15.61 rows=5 width=68) (actual
time=0.009..0.010 rows=1 loops=4)
 Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
 ->  Seq Scan on cms_menu c  (cost=0.00..14.40
rows=220 width=64) (actual time=0.002..0.004 rows=12 loops=3)
   Filter: cmenu_active
 ->  Hash  (cost=0.20..0.20 rows=10 width=36) (actual
time=0.002..0.002 rows=1 loops=4)
   Buckets: 1024  Batches: 1  Memory Usage: 8kB
   ->  WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.001 rows=1
loops=4)
   ->  Sort  (cost=1.28..1.40 rows=51 width=68) (actual
time=0.131..0.131 rows=1 loops=1)
 Sort Key: cte.depth
 Sort Method: top-N heapsort  Memory: 25kB
 ->  CTE Scan on cte  (cost=0.00..1.02 rows=51 width=68)
(actual time=0.024..0.073 rows=4 loops=1)
 Planning time: 0.221 ms
 Execution time: 0.163 ms
(19 rows)




=>explain analyze with recursive cte(cmenu_id,cmenu_parent) as (
SELECT cmenu_id,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte where cmenu_parent IS NULL LIMIT 1;

QUERY PLAN
---
 Limit  (cost=165.19..166.21 rows=1 width=64) (actual
time=0.069..0.069 rows=1 loops=1)
   CTE cte
 ->  Recursive Union  (cost=0.15..165.19 rows=51 width=64) (actual
time=0.020..0.064 rows=4 loops=1)
   ->  Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=64) (actual time=0.019..0.020 rows=1
loops=1)
 Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
 Filter: cmenu_active
   ->  Hash Join  (cost=0.33..15.60 rows=5 width=64) (actual
time=0.011..0.012 rows=1 loops=3)
 Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
 ->  Seq Scan on cms_menu c  (cost=0.00..14.40
rows=220 width=64) (actual time=0.003..0.005 rows=9 loops=3)
   Filter: cmenu_active
 ->  Hash  (cost=0.20..0.20 rows=10 width=32) (actual
time=0.002..0.002 rows=1 loops=3)
   Buckets: 1024  Batches: 1  Memory Usage: 9kB
   ->  WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=32) (actual time=0.001..0.001 rows=1
loops=3)
   ->  CTE Scan on cte  (cost=0.00..1.02 rows=1 width=64) (actual
time=0.068..0.068 rows=1 loops=1)
 Filter: (cmenu_parent IS NULL)
 Rows Removed by Filter: 3
 Planning time: 0.302 ms
 Execution time: 0.105 ms
(18 rows)

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Adrian Klaver]

On 04/16/2018 10:18 AM, Keith Fiske wrote:



On Mon, Apr 16, 2018 at 12:21 PM, Tom Lane > wrote:






So playing around with the "client_encoding" configuration option in 
postgresql.conf. According to the docs, setting this should set the 
default encoding for any client that connects, right?


https://www.postgresql.org/docs/10/static/runtime-config-client.html#GUC-CLIENT-ENCODING 



That description also seems misleading in saying that the default client 
encoding is what the database encoding is as well. At least as far as 
psql is concerned, right? I've tried setting that value in 
postgresql.conf but psql keeps setting it to UTF8 when I connect to the 
SQL_ASCII database unless I then specifically run "set client_encoding = 
'SQL_ASCII';"


Realized that while my previous suggestion of setting PGCLIENTENCODING 
to 'SQL_ASCII' would work it would also affect libpq programs beside psql.


For a finer grained approach set \encoding 'SQL_ASCII' in a .psqlrc file.:

https://www.postgresql.org/docs/10/static/app-psql.html

"Files

psqlrc and ~/.psqlrc

Unless it is passed an -X option, psql attempts to read and execute 
commands from the system-wide startup file (psqlrc) and then the user's 
personal startup file (~/.psqlrc), after connecting to the database but 
before accepting normal commands. These files can be used to set up the 
client and/or the server to taste, typically with \set and SET commands.


The system-wide startup file is named psqlrc and is sought in the 
installation's “system configuration” directory, which is most reliably 
identified by running pg_config --sysconfdir. By default this directory 
will be ../etc/ relative to the directory containing the PostgreSQL 
executables. The name of this directory can be set explicitly via the 
PGSYSCONFDIR environment variable.


The user's personal startup file is named .psqlrc and is sought in 
the invoking user's home directory. On Windows, which lacks such a 
concept, the personal startup file is named 
%APPDATA%\postgresql\psqlrc.conf. The location of the user's startup 
file can be set explicitly via the PSQLRC environment variable.


Both the system-wide startup file and the user's personal startup 
file can be made psql-version-specific by appending a dash and the 
PostgreSQL major or minor release number to the file name, for example 
~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific version-matching 
file will be read in preference to a non-version-specific file.

"



--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Adrian Klaver]

On 04/16/2018 10:18 AM, Keith Fiske wrote:







So playing around with the "client_encoding" configuration option in 
postgresql.conf. According to the docs, setting this should set the 
default encoding for any client that connects, right?


https://www.postgresql.org/docs/10/static/runtime-config-client.html#GUC-CLIENT-ENCODING 



That description also seems misleading in saying that the default client 
encoding is what the database encoding is as well. At least as far as 
psql is concerned, right? I've tried setting that value in 
postgresql.conf but psql keeps setting it to UTF8 when I connect to the 
SQL_ASCII database unless I then specifically run "set client_encoding = 
'SQL_ASCII';"


The order that parameters are evaluated for libpq programs is:

https://www.postgresql.org/docs/10/static/libpq-connect.html

"PQconnectdbParams

...


If any parameter is NULL or an empty string, the corresponding 
environment variable (see Section 33.14) is checked. If the environment 
variable is not set either, then the indicated built-in defaults are used.


...

"



--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Keith Fiske]

On Mon, Apr 16, 2018 at 12:30 PM, Vick Khera  wrote:

> Hi Keith,
>
> Not sure if this will help but a couple of years ago I migrated from an
> SQL_ASCII encoding to UTF8. The data was primarily ASCII with some windows
> garbage, and a little bit of UTF8 from customers filling out forms that
> were not specifically encoded anything.
>
> I wrote a utility that in-place scans and updates the tables in your
> SQL_ASCII-encoded database and ensures that everything is 100% UTF8 NFC at
> the end. For us, there were some characters in some bizarre local
> encodings, and we had to either toss or make educated guesses for them.
>
> After the cleaning, you dump with client encoding UTF8, then restore into
> the final database with UTF8 encoding.
>
> You can find it on my github along with documentation and tests to verify
> it works: https://github.com/khera/utf8-inline-cleaner
>
> On Mon, Apr 16, 2018 at 11:16 AM, Keith Fiske  > wrote:
>
>> Running into an issue with helping a client upgrade from 8.3 to 10 (yes,
>> I know, please keep the out of support comments to a minimum, thanks :).
>>
>> The old database was in SQL_ASCII and it needs to stay that way for now
>> unfortunately. The dump and restore itself works fine, but we're now
>> running into issues with some data returning encoding errors unless we
>> specifically set the client_encoding value to SQL_ASCII.
>>
>> Looking at the 8.3 database, it has the client_encoding value set to UTF8
>> and queries seem to work fine. Is this just a bug in the old 8.3 not
>> enforcing encoding properly?
>>
>> The other thing I noticed on the 10 instance was that, while the LOCALE
>> was set to SQL_ASCII, the COLLATE and CTYPE values for the restored
>> databases were en_US.UTF-8. Could this be having an affect? Is there any
>> way to see what these values were on the old 8.3 database? The pg_database
>> catalog does not have these values stored back then.
>>
>> --
>> Keith Fiske
>> Senior Database Engineer
>> Crunchy Data - http://crunchydata.com
>>
>
>

Thanks Vick! We will hopefully be helping them to get off SQL_ASCII after
this upgrade. Was challenging enough to get the upgrade itself done, so
doing the encoding migration at the same time unfortunately wasn't
possible. It's more than just the database that needs fixing, it's an
entire data ingestion process as well.

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Keith Fiske]

On Mon, Apr 16, 2018 at 12:21 PM, Tom Lane  wrote:

> Keith Fiske  writes:
> > On Mon, Apr 16, 2018 at 12:09 PM, Tom Lane  wrote:
> >> This is not a great idea, no.  You could be getting strange misbehaviors
> >> in e.g. string comparison, because strcoll() will expect UTF8 data and
> >> will likely not cope well with data that isn't valid in that encoding.
>
> > And pg_controldata was able to show that the CTYPE and COLLATE were UTF8
> on
> > the old system. If that's the case, do you still think it's a good idea
> to
> > set the COLLATE and CTYPE to "C"?
>
> Well, if the customer's been happy with the behavior of the system so far,
> maybe it's all right.  But this is sure the first thing I'd look at if
> there are any gripes about its behavior with non-UTF8 strings.  I'd be
> especially worried about this if you try to migrate the database to any
> new platform, as it's a bet about the behavior of libc not PG itself.
>
> regards, tom lane
>


This is going from RHEL 6.7 to RHEL 7.4

It is a dump and restore upgrade as well.

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Vick Khera]

Hi Keith,

Not sure if this will help but a couple of years ago I migrated from an
SQL_ASCII encoding to UTF8. The data was primarily ASCII with some windows
garbage, and a little bit of UTF8 from customers filling out forms that
were not specifically encoded anything.

I wrote a utility that in-place scans and updates the tables in your
SQL_ASCII-encoded database and ensures that everything is 100% UTF8 NFC at
the end. For us, there were some characters in some bizarre local
encodings, and we had to either toss or make educated guesses for them.

After the cleaning, you dump with client encoding UTF8, then restore into
the final database with UTF8 encoding.

You can find it on my github along with documentation and tests to verify
it works: https://github.com/khera/utf8-inline-cleaner

On Mon, Apr 16, 2018 at 11:16 AM, Keith Fiske 
wrote:

> Running into an issue with helping a client upgrade from 8.3 to 10 (yes, I
> know, please keep the out of support comments to a minimum, thanks :).
>
> The old database was in SQL_ASCII and it needs to stay that way for now
> unfortunately. The dump and restore itself works fine, but we're now
> running into issues with some data returning encoding errors unless we
> specifically set the client_encoding value to SQL_ASCII.
>
> Looking at the 8.3 database, it has the client_encoding value set to UTF8
> and queries seem to work fine. Is this just a bug in the old 8.3 not
> enforcing encoding properly?
>
> The other thing I noticed on the 10 instance was that, while the LOCALE
> was set to SQL_ASCII, the COLLATE and CTYPE values for the restored
> databases were en_US.UTF-8. Could this be having an affect? Is there any
> way to see what these values were on the old 8.3 database? The pg_database
> catalog does not have these values stored back then.
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
>

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Tom Lane]

Keith Fiske  writes:
> On Mon, Apr 16, 2018 at 12:09 PM, Tom Lane  wrote:
>> This is not a great idea, no.  You could be getting strange misbehaviors
>> in e.g. string comparison, because strcoll() will expect UTF8 data and
>> will likely not cope well with data that isn't valid in that encoding.

> And pg_controldata was able to show that the CTYPE and COLLATE were UTF8 on
> the old system. If that's the case, do you still think it's a good idea to
> set the COLLATE and CTYPE to "C"?

Well, if the customer's been happy with the behavior of the system so far,
maybe it's all right.  But this is sure the first thing I'd look at if
there are any gripes about its behavior with non-UTF8 strings.  I'd be
especially worried about this if you try to migrate the database to any
new platform, as it's a bet about the behavior of libc not PG itself.

regards, tom lane

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Keith Fiske]

On Mon, Apr 16, 2018 at 12:09 PM, Tom Lane  wrote:

> Keith Fiske  writes:
> > Running into an issue with helping a client upgrade from 8.3 to 10 (yes,
> I
> > know, please keep the out of support comments to a minimum, thanks :).
>
> > The old database was in SQL_ASCII and it needs to stay that way for now
> > unfortunately. The dump and restore itself works fine, but we're now
> > running into issues with some data returning encoding errors unless we
> > specifically set the client_encoding value to SQL_ASCII.
>
> I'm guessing you might be hitting this 9.1 change:
>
> * Have psql set the client encoding from the operating system locale
>   by default (Heikki Linnakangas)
>
>   This only happens if the PGCLIENTENCODING environment variable is
>   not set.
>
> I think the previous default was to set client encoding equal to the
> server encoding.
>
> > Looking at the 8.3 database, it has the client_encoding value set to UTF8
> > and queries seem to work fine. Is this just a bug in the old 8.3 not
> > enforcing encoding properly?
>
> Somewhere along the line we made SQL_ASCII -> something else conversions
> check that the data was valid per the other encoding, even though no
> actual data change happens.
>
> > The other thing I noticed on the 10 instance was that, while the LOCALE
> was
> > set to SQL_ASCII,
>
> You mean encoding, I assume.
>
> > the COLLATE and CTYPE values for the restored databases
> > were en_US.UTF-8. Could this be having an affect?
>
> This is not a great idea, no.  You could be getting strange misbehaviors
> in e.g. string comparison, because strcoll() will expect UTF8 data and
> will likely not cope well with data that isn't valid in that encoding.
>
> If you can't sanitize the encoding of your data, I'd suggest running
> with lc_collate and lc_ctype set to "C".
>
> regards, tom lane
>


Thanks to both of you Adrian & Tom.

It is the 9.1 change to the psql client that seems to be causing this.

And pg_controldata was able to show that the CTYPE and COLLATE were UTF8 on
the old system. If that's the case, do you still think it's a good idea to
set the COLLATE and CTYPE to "C"?


-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Tom Lane]

Keith Fiske  writes:
> Running into an issue with helping a client upgrade from 8.3 to 10 (yes, I
> know, please keep the out of support comments to a minimum, thanks :).

> The old database was in SQL_ASCII and it needs to stay that way for now
> unfortunately. The dump and restore itself works fine, but we're now
> running into issues with some data returning encoding errors unless we
> specifically set the client_encoding value to SQL_ASCII.

I'm guessing you might be hitting this 9.1 change:

* Have psql set the client encoding from the operating system locale
  by default (Heikki Linnakangas)

  This only happens if the PGCLIENTENCODING environment variable is
  not set.

I think the previous default was to set client encoding equal to the
server encoding.

> Looking at the 8.3 database, it has the client_encoding value set to UTF8
> and queries seem to work fine. Is this just a bug in the old 8.3 not
> enforcing encoding properly?

Somewhere along the line we made SQL_ASCII -> something else conversions
check that the data was valid per the other encoding, even though no
actual data change happens.

> The other thing I noticed on the 10 instance was that, while the LOCALE was
> set to SQL_ASCII,

You mean encoding, I assume.

> the COLLATE and CTYPE values for the restored databases
> were en_US.UTF-8. Could this be having an affect?

This is not a great idea, no.  You could be getting strange misbehaviors
in e.g. string comparison, because strcoll() will expect UTF8 data and
will likely not cope well with data that isn't valid in that encoding.

If you can't sanitize the encoding of your data, I'd suggest running
with lc_collate and lc_ctype set to "C".

regards, tom lane

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

[Adrian Klaver]

On 04/16/2018 08:16 AM, Keith Fiske wrote:
Running into an issue with helping a client upgrade from 8.3 to 10 (yes, 
I know, please keep the out of support comments to a minimum, thanks :).


The old database was in SQL_ASCII and it needs to stay that way for now 
unfortunately. The dump and restore itself works fine, but we're now 
running into issues with some data returning encoding errors unless we 
specifically set the client_encoding value to SQL_ASCII.


Looking at the 8.3 database, it has the client_encoding value set to 
UTF8 and queries seem to work fine. Is this just a bug in the old 8.3 
not enforcing encoding properly?e


AFAIK, SQL_ASCII basically means no encoding:

https://www.postgresql.org/docs/10/static/multibyte.html

"The SQL_ASCII setting behaves considerably differently from the other 
settings. When the server character set is SQL_ASCII, the server 
interprets byte values 0-127 according to the ASCII standard, while byte 
values 128-255 are taken as uninterpreted characters. 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. In most cases, if you 
are working with any non-ASCII data, it is unwise to use the SQL_ASCII 
setting because PostgreSQL will be unable to help you by converting or 
validating non-ASCII characters."



What client are you working with?

If psql then its behavior has changed between 8.3 and 10:

https://www.postgresql.org/docs/10/static/release-9-1.html#id-1.11.6.121.3

"

Have psql set the client encoding from the operating system locale by 
default (Heikki Linnakangas)


This only happens if the PGCLIENTENCODING environment variable is not set.
"

https://www.postgresql.org/docs/10/static/app-psql.html

"If both standard input and standard output are a terminal, then psql 
sets the client encoding to “auto”, which will detect the appropriate 
client encoding from the locale settings (LC_CTYPE environment variable 
on Unix systems). If this doesn't work out as expected, the client 
encoding can be overridden using the environment variable PGCLIENTENCODING."





The other thing I noticed on the 10 instance was that, while the LOCALE 
was set to SQL_ASCII, the COLLATE and CTYPE values for the restored 
databases were en_US.UTF-8. Could this be having an affect? Is there any 
way to see what these values were on the old 8.3 database? The 
pg_database catalog does not have these values stored back then.


--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: SQL statement in an error report for deferred constraint violation.

[Jack Gao]

Konrad Witaszczyk wrote
> Hi,
> 
> While PQresultErrorField() from libpq allows to get context in which an
> error

> occurred for immediate constraints, and thus an SQL statement which caused
> the
> constraint violation, I cannot see any way to find out which SQL statement
> caused an error in case of deferred constraints, in particular deferred
> foreign
> key constraints.
> 
> Is there any way to check which SQL statement or at least which row
> violated a
> constraint when it's deferred? If not does anyone know why there is such
> restriction?
> 
> 
> Konrad
> 
> 
> 
> signature.asc (981 bytes)
> http://www.postgresql-archive.org/attachment/6015088/0/signature.asc;

First of all, you need to locate the problem SQL by modifying log
parameters.

sed -ir "s/#*logging_collector.*/logging_collector= on/"
$PGDATA/postgresql.conf
sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/"
$PGDATA/postgresql.conf
sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf

Execute this SQL and send error message in the log.

regards

Jack Gao



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Data migration from postgres 8.4 to 9.4

[Amitabh Kant]

On Mon, Apr 16, 2018 at 12:33 PM, Akshay Ballarpure <
akshay.ballarp...@tcs.com> wrote:

> Hello,
> I need help in using postgresql 8.4 data in postgres 9.4 version. Do I
> need to run any tool to achieve the same?
>
> Steps i followed is ran postgresql 8.4 and 9.4, copied data from 8.4
> instance to 9.4 and try to start postgresql 9.4 but no luck, getting below
> error.
>
> [root@ms-esmon esm-data]# su - postgres -c 
> "/opt/rh/rh-postgresql94/root/usr/bin/postgres
> -D /var/ericsson/esm-data/postgresql-data/ 2>&1 &"
> [root@ms-esmon esm-data]# LOG:  skipping missing configuration file
> "/var/ericsson/esm-data/postgresql-data/postgresql.auto.conf"
> 2018-04-16 06:52:01.546 GMT  *FATAL*:  database files are incompatible
> with server
> 2018-04-16 06:52:01.546 GMT  *DETAIL*:  The data directory was
> initialized by PostgreSQL version 8.4, which is not compatible with this
> version 9.4.9.
>
>
> With Best Regards
> Akshay
> Ericsson OSS MON
> Tata Consultancy Services
> Mailto: akshay.ballarp...@tcs.com
> Website: http://www.tcs.com


You cannot simply copy data between major versions. Look into pg_upgrade
utility to upgrade your database, or you could use pg_dump/pg_restore to
migrate between major versions.

Amitabh