When should parameters be passed as text v. binary?

[Paula Kirsch]

I'm just trying to understand the trade-offs between sending everything
always as text, all integer parameters as binary, floats as binary, etc.

Changing default ../data/ directory

[Rich Shepard]

I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data
directory has always been located in /var/lib/pgsql//data. This
data directory is located in the / partition on a 240G SSD.

There's another 2T HDD with a /data partition and I want to both move the
current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future
databases use that location.

The PGDATA environment variable used to be used for this (but I never did
get it satisfactorily working). Web searches show changing initdb or,
perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but
no postgresql.conf).

As this is all new to me I want to learn how to:

1) Define a postgresl.conf and learn where it should be located.

2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.

3) Create all new databases in /data/pgsql/11 by default.

I'm sure it's in the manual yet I'm unsure just where to start reading.

Rich

Re: Changing default ../data/ directory

[nikhil raj]

Hi Rich Shepard,

Step 1-: Stop the postgres services and change the path of the
data directory in postgres.conf file.

Step 2-: rsync the flie to new path. And start the service.

Step 3-: connect to postgres and check command output from psql shell SHOW
data_directory;  should see now data path.

On Sat, 4 Jan 2020, 8:50 pm Rich Shepard,  wrote:

> I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data
> directory has always been located in /var/lib/pgsql//data. This
> data directory is located in the / partition on a 240G SSD.
>
> There's another 2T HDD with a /data partition and I want to both move the
> current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future
> databases use that location.
>
> The PGDATA environment variable used to be used for this (but I never did
> get it satisfactorily working). Web searches show changing initdb or,
> perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but
> no postgresql.conf).
>
> As this is all new to me I want to learn how to:
>
> 1) Define a postgresl.conf and learn where it should be located.
>
> 2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.
>
> 3) Create all new databases in /data/pgsql/11 by default.
>
> I'm sure it's in the manual yet I'm unsure just where to start reading.
>
> Rich
>
>
>

Re: When should parameters be passed as text v. binary?

[Adrian Klaver]

On 1/4/20 3:54 AM, Paula Kirsch wrote:
I'm just trying to understand the trade-offs between sending everything 
always as text, all integer parameters as binary, floats as binary, etc.


From where to where and using what?


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

Re: Changing default ../data/ directory

[Adrian Klaver]

On 1/4/20 7:20 AM, Rich Shepard wrote:

I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data
directory has always been located in /var/lib/pgsql//data. This
data directory is located in the / partition on a 240G SSD.

There's another 2T HDD with a /data partition and I want to both move the
current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future
databases use that location.

The PGDATA environment variable used to be used for this (but I never did
get it satisfactorily working). Web searches show changing initdb or,
perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but
no postgresql.conf).


Not following above:

1) Are you looking for your current postgresql.conf?

2) Some examples of postgresql.conf?

More below.


As this is all new to me I want to learn how to:

1) Define a postgresl.conf and learn where it should be located.

2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.

3) Create all new databases in /data/pgsql/11 by default.

I'm sure it's in the manual yet I'm unsure just where to start reading.

Rich




https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY


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

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, nikhil raj wrote:


Step 1-: Stop the postgres services and change the path of the
data directory in postgres.conf file.


The postgresql.conf.sample notes that the default value of data_directory is
taken from -D in the startup command or the PGDATA environment variable. I
suppose that I can define PGDATA in ~/.bash_profile as I'm the only user.

Thanks,

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Adrian Klaver wrote:


Not following above:
1) Are you looking for your current postgresql.conf?


Yes.


More below.
https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY


Thanks, Adrian.

Rich

Re: Changing default ../data/ directory

[Jeff Janes]

On Sat, Jan 4, 2020 at 10:20 AM Rich Shepard 
wrote:

>
> The PGDATA environment variable used to be used for this (but I never did
> get it satisfactorily working).


PGDATA should work fine if you always start the server directly.  But if
you sudo or su to another user, they likely won't inherit your environment
variables.  And if you use some kind of start-up script, they will likely
override it.  How do you start and stop PostgreSQL?


> I'm sure it's in the manual yet I'm unsure just where to start reading.
>

If you are using start-up scripts that come with some installation package,
then the details are up to the packager.  The PostgreSQL manual is of
little use in knowing what the packager decided to do.  It will describe
what the pieces are, but not how they got assembled together.

Cheers,

Jeff

Re: Changing default ../data/ directory

[George Neuner]

On Sat, 4 Jan 2020 07:20:44 -0800 (PST), Rich Shepard
 wrote:

>As this is all new to me I want to learn how to:
>
>1) Define a postgresl.conf and learn where it should be located.
>
>2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.
>
>3) Create all new databases in /data/pgsql/11 by default.
>
>I'm sure it's in the manual yet I'm unsure just where to start reading.
>
>Rich

Since the new drive is local you can just move the data directory to
its new location and link to it from the default (/var) location.  No
configuration changes needed.

Won't help you learn necessarily, but solves the problem.

YMMV,
George

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Jeff Janes wrote:


PGDATA should work fine if you always start the server directly. But if
you sudo or su to another user, they likely won't inherit your environment
variables. And if you use some kind of start-up script, they will likely
override it. How do you start and stop PostgreSQL?


Jeff,

Slackware's /etc/rc.d/rc.postgresql start command. I can modify that script
to change the data directory path.

Thanks,

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, George Neuner wrote:


Since the new drive is local you can just move the data directory to its
new location and link to it from the default (/var) location. No
configuration changes needed.


George,

Huh! It didn't occur to me to make a softlink to the new directory from the
old one. That looks like the most parsimonious solution.

Thanks,

Rich

Re: Changing default ../data/ directory

[Adrian Klaver]

On 1/4/20 8:27 AM, Rich Shepard wrote:

On Sat, 4 Jan 2020, Adrian Klaver wrote:


Not following above:
1) Are you looking for your current postgresql.conf?


Yes


I don't see anything here:

https://slackbuilds.org/slackbuilds/14.2/system/postgresql/rc.postgresql.new

that changes the conf location, so postgresql.conf should be in the DATADIR:

/var/lib/pgsql/$PG_VERSION/data




More below.
https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY 



Thanks, Adrian.

Rich





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

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Adrian Klaver wrote:


I don't see anything here:
https://slackbuilds.org/slackbuilds/14.2/system/postgresql/rc.postgresql.new
that changes the conf location, so postgresql.conf should be in the DATADIR:
/var/lib/pgsql/$PG_VERSION/data


Adrian,

How interesting. When I used 'locate postgresql.conf' it did not find it,
only /usr/share/postgresql-11/postgresql.conf.sample. That's because users
are not allowed to see what's in /var/lib/. I don't recall running into this
issue before.

Thanks,

Rich

Undeliverable: Re: Changing default ../data/ directory (fwd)

[Rich Shepard]

I could not find an address for the pgsql-general list owner. Please excuse
my posting to the entire list.

Today all my messages generate this response:

-- Forwarded message --
Date: Sat, 4 Jan 2020 18:21:10 +
From: postmas...@outlook.com
To: rshep...@appl-ecosys.com
Subject: Undeliverable: Re: Changing default ../data/ directory

p3plibsmtp01-13.prod.phx3.secureserver.net rejected your message to the 
following email addresses:

p...@paulweiss.info
The address you sent your message to wasn't found at the destination domain.
It might be misspelled or it might not exist. Try to fix the problem by
doing one or more of the following:

p3plibsmtp01-13.prod.phx3.secureserver.net gave this error:
104.47.55.172 is not allowed to send from  per its SPF Record. 
Please inspect your SPF settings, and try again. IB508 

Diagnostic information for administrators:

Generating server: BN8NAM12HT135.mail.protection.outlook.com

--

Please remove this address from the subscribed list.

Thanks in advance,

Rich

Re: When should parameters be passed as text v. binary?

[Andrew Gierth]

> "Paula" == Paula Kirsch  writes:

 Paula> I'm just trying to understand the trade-offs between sending
 Paula> everything always as text, all integer parameters as binary,
 Paula> floats as binary, etc.

For passing data from client to server, there's no particular reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query, rather
than just leaving it as unknown).

For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.

-- 
Andrew (irc:RhodiumToad)

Re: When should parameters be passed as text v. binary?

[Justin]

As noted by Adrian what is the USE CASE

As a general rule one wants to use the format the data is being stored in.
every time data is cast to another type its going to eat those all so
precious CPU cycles.  (all the horror of electrons turned into infrared
beams)

converting Bytea type to a string encoded in Base64 adds 30% overhead.
converting an integer tor ASCII can add allot of overhead.

The answer is it depends on the USE CASE if casting adds any benefit.  my
gut tells me it will not add any benefiet



On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth 
wrote:

> > "Paula" == Paula Kirsch  writes:
>
>  Paula> I'm just trying to understand the trade-offs between sending
>  Paula> everything always as text, all integer parameters as binary,
>  Paula> floats as binary, etc.
>
> For passing data from client to server, there's no particular reason not
> to use the binary format for any data type that you understand (and
> where you're passing the data type oid explicitly in the query, rather
> than just leaving it as unknown).
>
> For results, things are harder, because libpq is currently
> all-or-nothing about result type formats, and if you start using
> extension types then not all of them even _have_ a binary format. And to
> decode a binary result you need to know the type, and have code to
> handle every specific type's binary format.
>
> --
> Andrew (irc:RhodiumToad)
>
>
>

Re: When should parameters be passed as text v. binary?

[Adrian Klaver]

On 1/4/20 11:25 AM, Paula Kirsch wrote:

Please reply to list also.
Ccing list.

In an extended-query, my understanding is that you can choose to pass 
the parameters as text or binary. I am trying to understand 
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY 
and 
https://www.postgresql.org/docs/current/protocol-message-formats.html. 
Thus the question about the benefits/detriments of passing the 
parameters as binary or text.


I am also having difficulty finding the postgresql documentation for the 
data type oid to pass to explicitly type the parameters.


https://www.postgresql.org/docs/11/catalog-pg-type.html



Any suggestions or examples of parse-messages with a couple of 
parameters being passed would be appreciated.


Thank you.

On Sat, Jan 4, 2020 at 11:18 AM Adrian Klaver > wrote:


On 1/4/20 3:54 AM, Paula Kirsch wrote:
 > I'm just trying to understand the trade-offs between sending
everything
 > always as text, all integer parameters as binary, floats as
binary, etc.

  From where to where and using what?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: How can I set a timeout for a locked table in Function ?

[Jeff Janes]

On Fri, Jan 3, 2020 at 1:05 PM Thomas Kellerer  wrote:

> Michael Lewis schrieb am 03.01.2020 um 18:00:
>
> > Why take an exclusive lock on an entire table to update a single row?
>
> That's what I was asking myself as well.
>


Note that the code takes "row exclusive", not "exclusive".  It is several
notches less restrictive, and is the mode an UPDATE statement is going to
take anyway.  It still isn't clear why he is doing it, however.

Cheers,

Jeff

Re: When should parameters be passed as text v. binary?

[Paula Kirsch]

Good point and I loved the way you put it. More low level stuff I need to
learn.

I'm still struggling trying to find the list of data type oids either in
the documentation or in the postgresql source code so that I can specify
the data correctly (assuming, of course, I make sure the binary on both
sides is compatible.

Thank you.

On Sat, Jan 4, 2020 at 3:30 PM Justin  wrote:

> As noted by Adrian what is the USE CASE
>
> As a general rule one wants to use the format the data is being stored
> in.  every time data is cast to another type its going to eat those all so
> precious CPU cycles.  (all the horror of electrons turned into infrared
> beams)
>
> converting Bytea type to a string encoded in Base64 adds 30% overhead.
> converting an integer tor ASCII can add allot of overhead.
>
> The answer is it depends on the USE CASE if casting adds any benefit.  my
> gut tells me it will not add any benefiet
>
>
>
> On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth 
> wrote:
>
>> > "Paula" == Paula Kirsch  writes:
>>
>>  Paula> I'm just trying to understand the trade-offs between sending
>>  Paula> everything always as text, all integer parameters as binary,
>>  Paula> floats as binary, etc.
>>
>> For passing data from client to server, there's no particular reason not
>> to use the binary format for any data type that you understand (and
>> where you're passing the data type oid explicitly in the query, rather
>> than just leaving it as unknown).
>>
>> For results, things are harder, because libpq is currently
>> all-or-nothing about result type formats, and if you start using
>> extension types then not all of them even _have_ a binary format. And to
>> decode a binary result you need to know the type, and have code to
>> handle every specific type's binary format.
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>>
>>

Re: When should parameters be passed as text v. binary?

[Adrian Klaver]

On 1/4/20 2:13 PM, Paula Kirsch wrote:
Good point and I loved the way you put it. More low level stuff I need 
to learn.


I'm still struggling trying to find the list of data type oids either in 
the documentation or in the postgresql source code so that I can specify 
the data correctly (assuming, of course, I make sure the binary on both 
sides is compatible.




https://www.postgresql.org/docs/11/catalog-pg-type.html

select oid, typname from pg_type;

If you want the source code version:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_type.dat;h=fe2c4eabb46dac36297699366d7574824238ecf2;hb=HEAD



Thank you.

On Sat, Jan 4, 2020 at 3:30 PM Justin > wrote:


As noted by Adrian what is the USE CASE

As a general rule one wants to use the format the data is being
stored in.  every time data is cast to another type its going to eat
those all so precious CPU cycles.  (all the horror of electrons
turned into infrared beams)

converting Bytea type to a string encoded in Base64 adds 30%
overhead.  converting an integer tor ASCII can add allot of overhead.

The answer is it depends on the USE CASE if casting adds any
benefit.  my gut tells me it will not add any benefiet



On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth
mailto:and...@tao11.riddles.org.uk>>
wrote:

 > "Paula" == Paula Kirsch mailto:pl.kir...@gmail.com>> writes:

  Paula> I'm just trying to understand the trade-offs between
sending
  Paula> everything always as text, all integer parameters as
binary,
  Paula> floats as binary, etc.

For passing data from client to server, there's no particular
reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query,
rather
than just leaving it as unknown).

For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary
format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.

-- 
Andrew (irc:RhodiumToad)






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