Re: Peer authentication failed ???

[Amn Ojee Uw]

Yes!
Thanks so much it worked!

On 10/3/23 9:37 a.m., Peter J. Holzer wrote:

On 2023-10-03 05:55:51 -0400, Amn Ojee Uw wrote:

psql -U my_group_worker -d my_group_db

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  Peer authentication failed for user "my_group_worker"

*** What am I doing wrong?

PostgreSQL uses the first matching rule from pg_hba.conf. Presumably
(because that's the default on Debian/Ubuntu) you have it set up to use
peer authentication on the unix socket and password authentication
(scram or md5) on everything else.

You are connecting via the Unix socket (/var/run/postgresql/.s.PGSQL.5432),
so the server will attempt peer authentication and nothing else. To get
it to attempt password authentication connect via a tcp socket:

psql -U my_group_worker -h localhost -d my_group_db

(More conveniently set up ident authentication for your users, then you
don't need a password.)

 hp

Peer authentication failed ???

[Amn Ojee Uw]

Hi there.

Using the 'sudo -u postgres psql' command I created a group and two 
accounts in the group, I also created a database for one of the 
accounts. Now, I'd like to login PG-15, using my new account and 
connecting to my new database, but PG-15 complains. See the example 
below, I think it is clear than my English.


*CREATE ROLE my_group; // Create a group**
**CREATE ROLE my_group_admin LOGIN PASSWORD 'AdminPassword' CREATEDB 
CREATEROLE; // Create admin**
**CREATE ROLE my_group_worker LOGIN PASSWORD 'WorkerPassword' CREATEDB; 
// Create Worker**

**GRANT my_group TO my_group_admin WITH INHERIT TRUE;**
**GRANT my_group TO my_group_worker WITH INHERIT TRUE;**
**
**CREATE DATABASE my_group_db WITH OWNER my_group_worker;**
**\q**
**psql -U my_group_worker -d my_group_db*

/psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_group_worker/*"*


*** What am I doing wrong?


Thanks in advance.

Re: [EXT] YNT: Need help tuning a query

[Amn Ojee Uw]

Wow!! This is what I call cryptic!!

On 9/29/23 2:46 a.m., Vladimir Sitnikov wrote:

Oh, I misplaced the added where conditions.
It should have been as follows, however, the overall idea is the same

--- orignial.sql
+++ tuned_v2.sql
@@ -83,6 +83,7 @@
                                AND (judg1.jrt_opt_out_flag <> 'Y' OR 
judg1.jrt_opt_out_flag IS NULL)

                     ) sub0
                LEFT OUTER JOIN
+                    LATERAL
                    (  SELECT sub4.case_year_number,
                                  sub4.judge_wld_id,
                                  sub4.judge_id,
@@ -99,6 +100,7 @@
                                  jrtf1.higher_judge_id,
                                  jrtf1.case_document_id
                             ) sub4
+                     WHERE sub4.judge_id = sub0.judge_id
                 GROUP BY sub4.case_year_number,
                                 sub4.judge_wld_id,
                                 sub4.judge_id,
@@ -106,6 +108,7 @@
                   ) sub1
                ON sub1.judge_id = sub0.judge_id
              LEFT OUTER JOIN
+                  LATERAL
                  (SELECT sub5.case_year_number,
                                sub5.judge_wld_id,
                                sub5.judge_id,
@@ -129,6 +132,7 @@
                                ),
                                jrtf2.case_document_id
                               ) sub5
+                  WHERE sub5.judge_id = sub0.judge_id
               GROUP BY sub5.case_year_number,
                               sub5.judge_wld_id,
                               sub5.judge_id,

SELECT agg_sub.judge_id,
       agg_sub.display_name,
       agg_sub.active_flag,
       agg_sub.judge_court_level,
       agg_sub.jrt_fact_first_year_trial,
       agg_sub.jrt_fact_last_year_trial,
       agg_sub.jrt_fact_totalcount_trial,
       agg_sub.filtered_first_year_trial,
       agg_sub.filtered_last_year_trial,
       agg_sub.jrt_fact_count_trial,
       agg_sub.jrt_fact_first_year_appeal,
       agg_sub.jrt_fact_last_year_appeal,
       agg_sub.jrt_fact_totalcount_appeal,
       agg_sub.filtered_first_year_appeal,
       agg_sub.filtered_last_year_appeal,
       agg_sub.jrt_fact_count_appeal,
       appellate_flag_sub.appellate_flag
   FROM (SELECT sub3.judge_id,
             sub3.display_name,
             sub3.active_flag,
             sub3.judge_court_level,
             (MIN(sub3.trial_unfilt_case_year_number)) AS 
jrt_fact_first_year_trial,
             (MAX(sub3.trial_unfilt_case_year_number)) AS 
jrt_fact_last_year_trial,
             (SUM(sub3.trial_unfilt_subcount)) AS 
jrt_fact_totalcount_trial,
             (MIN(sub3.trial_filt_case_year_number)) AS 
filtered_first_year_trial,
             (MAX(sub3.trial_filt_case_year_number)) AS 
filtered_last_year_trial,

             (SUM(sub3.trial_filt_subcount)) AS jrt_fact_count_trial,
             (MIN(sub3.appeal_unfilt_case_year_number)) AS 
jrt_fact_first_year_appeal,
             (MAX(sub3.appeal_unfilt_case_year_number)) AS 
jrt_fact_last_year_appeal,
             (SUM(sub3.appeal_unfilt_subcount)) AS 
jrt_fact_totalcount_appeal,
             (MIN(sub3.appeal_filt_case_year_number)) AS 
filtered_first_year_appeal,
             (MAX(sub3.appeal_filt_case_year_number)) AS 
filtered_last_year_appeal,

             (SUM(sub3.appeal_filt_subcount)) AS jrt_fact_count_appeal
           FROM (SELECT sub0.judge_id,
                    sub0.display_name,
                    sub0.active_flag,
                    sub0.judge_court_level,
                    (CASE WHEN sub2.grouping_flg = 'T' AND 
sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.case_year_number ELSE 
NULL END) AS trial_unfilt_case_year_number,
                    (CASE WHEN sub2.grouping_flg = 'T'AND 
sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.subcount ELSE NULL END 
                      ) AS trial_unfilt_subcount,
                    (CASE WHEN sub2.grouping_flg = 'T' AND 
sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN 
sub2.case_year_number BETWEEN sub0.low_case_year_number AND 
sub0.high_case_year_number
                           THEN sub2.case_year_number ELSE NULL END) 
ELSE NULL END) AS trial_filt_case_year_number,
                    (CASE WHEN sub2.grouping_flg = 'T' AND 
sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN 
sub2.case_year_number BETWEEN sub0.low_case_year_number AND 
sub0.high_case_year_number
                           THEN sub2.subcount ELSE NULL END       ) 
ELSE NULL END        ) AS trial_filt_subcount,
                    (CASE WHEN sub1.grouping_flg = 'A'AND 
sub1.judge_wld_id = sub0.judge_wld_id THEN sub1.case_year_number WHEN 
sub2.grouping_flg = 'A'   AND sub2.judge_wld_id = sub0.judge_wld_id
                           THEN sub2.case_year_number ELSE NULL END   
      ) AS appeal_unfilt_case_year_number,

                    (
                    CASE WHEN sub1.grouping_flg = 'A'
                    AND sub1.judge_wld_id = sub0.judge_wld_id 

Re: PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

I see now, any suggestions as work around in JDBC?

Thanks!!

On 9/3/23 8:12 p.m., David G. Johnston wrote:

On Sunday, September 3, 2023, Amn Ojee Uw  wrote:

Are you saying that JDBC cannot handle or process \gexec, since it
is PG-15 exclusive?


psql is a client application present in all versions of PostgreSQL.  
It has a bunch of features related to executing SQL queries.  The 
stuff that it is documented to do are only doable by it in many cases, 
and in all cases are done locally, not by the server.  You cannot send 
those meta-commands to the server, it has no clue what to do with 
them.  And since you are using JDBC you by definition aren’t using psql.


David J.

Re: PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

Thanks Adrian, I did read the link you suggested.
I was had at the Introduction title, since it makes this mention 
"/Alternatively, input can be from a file or from command line 
arguments./" since the input would be from the back end of the database, 
as I am using Java DBC to access the database.


However, I have not been able to discern the correlation that exist 
between the*JDBC statement *and the definition of *gexec* [/Sends the 
current query buffer to the server, then treats each column of each row 
of the query's output (if any) as an SQL statement to be executed./]


I guess it is that I am so new to the world of PG-15 that I just cannot 
understand or get your point. My reality is that esoteric definition go 
right over my head, since I am a newbie.


Would you be able to make the appropriate corrections to the 
PostgreSQL-15 schema passed to the JDBC Statement.executeUpdate() method?


Thanks in advance.

PD:

Are you saying that JDBC cannot handle or process \gexec, since it is 
PG-15 exclusive?


On 9/3/23 12:04 p.m., Adrian Klaver wrote:

On 9/3/23 09:00, Amn Ojee Uw wrote:

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a 
parameter in a JDBC method?


The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.

//CREATE DATABASE IF NOT EXISTS
//~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**



\gexec is a psql specific meta-command.

See Meta-Commands here:

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




Any help would be very much appreciated.

Thanks in advance.

PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a parameter 
in a JDBC method?


The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.

//CREATE DATABASE IF NOT EXISTS
//~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**

**
**stmt.executeUpdate(s.toString());*

However, this time PostgreSQL-15 complains with an error message saying :

*Exception in thread "main" org.postgresql.util.PSQLException: ERROR: 
syntax error at or near "\"**

**  Position: 122

*

For better clarification, I'd like to show the code to create the schema 
inside Java/JDBC.

 snip 

 */
   ...

    var sv = myString();
    ...

    public final myString getCreateDatabase(myString s){
    this.sv.setData("SELECT 'CREATE DATABASE ");
    this.sv.append(s.toString());
    this.sv.append("\'");
    this.sv.append("WHERE NOT EXISTS (SELECT FROM pg_database WHERE 
datname = ");

    this.sv.append("\'");
    this.sv.append(s.toString());
    this.sv.append("\'");
    this.sv.append(")\\gexec");

    return sv;
    }


Any help would be very much appreciated.

Thanks in advance.

Re: JDBC + PG-15 but not psql

[Amn Ojee Uw]

Yes your are right! I don't know why it was part of the requirement to 
add a JDBC line to explicitly set the behaviour of autocommint to 
*true*, when it was the default value. Perhaps it has to do with "just 
making sure it is" sort of thing.

Thanks Dave, I'll let the my group know.

On 8/22/23 8:31 a.m., Dave Cramer wrote:

Hi Amn,

What I don't understand is the requirement to set autocommit to true 
since this is the default ?


Either way this is expected behaviour as psql also has autocommit true 
by default.


Thanks,

Dave Cramer



On Mon, 21 Aug 2023 at 20:57, Amn Ojee Uw  wrote:

Thanks Dave for your interest.

In  the OP I started by explaining the difficulties when trying to
create a tabelspace using JDBC; please note that when using the
PG-15 prompt I did not have the problem. Well, it turns out that
while developing the request for help (writing the email), I
realize that my code had the following line :
*this.conn.setAutoCommit(false);*/*
*/However, in my research, I had read a post mentioning that by
setting /auto commit/ to *true* the issue was solved. So, I
changed that line of code in my algorithm to :
*this.conn.setAutoCommit(true);*/*
*/Which also resulted in my app working without any errors./**/

Please read the code-snip I submitted; there you can see that line
of code I edited; which I commented with "/*//*//*game changer!!".*/

Thanks again Dave, have great week.

/*
*/

On 8/21/23 2:51 p.m., Dave Cramer wrote:

Hi Amn,

Can you help me understand this issue better?

I don't see anywhere in the code where you are attempting to
create a tablespace ?


Dave Cramer



On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw 
wrote:

Hello.

I have searched the net in an attempt to find if others have
had and resolved this challenge, but most of the sites talk
about how, when using the psql, this error arises. In my
case, the error arises only when access PG-15 using JDBC.
JDBC connects to the database, but when trying to execute a
schema, it be to create a database or to create a tabelspace
I get this error :

*StackTrace : [Ljava.lang.StackTraceElement;@7a69b07**
**Message : ERROR: CREATE TABLESPACE cannot run inside a
transaction block*

I have used the same algorithm, but now modify to accommodate
PG-15, when using SQLite. So the JDBC code might not be the
problem, but the requirements needed from PG-15.

Change of perspective, I resolved the concern!

Here is what changed the outcome :
--- snip ---

public void connectToDatabase() throws
ClassNotFoundException, SQLException {
    try {
Class.forName(this.getDatabaseClass().toString());
    this.conn =
DriverManager.getConnection(this.getDatabaseUrl().toString(),
    this.getUserID().toString(),
this.getUserPassword().toString());

*this.conn.setAutoCommit(true);*/*//*//*game changer!!*/

        this.pout("Connected to the PostgreSQL server,
success!!!");

    this.stmt = this.conn.createStatement();
    } catch (final SQLException | ClassNotFoundException e) {
    throw e;
    }
}

--

All I had to do was to setAutoCommit to true, PG-15 set this
value to false by default.


I hope my experience can help others.

Re: JDBC + PG-15 but not psql

[Amn Ojee Uw]

Thanks Dave for your interest.

In  the OP I started by explaining the difficulties when trying to 
create a tabelspace using JDBC; please note that when using the PG-15 
prompt I did not have the problem. Well, it turns out that while 
developing the request for help (writing the email), I realize that my 
code had the following line :

*this.conn.setAutoCommit(false);*/*
*/However, in my research, I had read a post mentioning that by setting 
/auto commit/ to *true* the issue was solved. So, I changed that line of 
code in my algorithm to :

*this.conn.setAutoCommit(true);*/*
*/Which also resulted in my app working without any errors./**/

Please read the code-snip I submitted; there you can see that line of 
code I edited; which I commented with "/*//*//*game changer!!".*/


Thanks again Dave, have great week.

/*
*/

On 8/21/23 2:51 p.m., Dave Cramer wrote:

Hi Amn,

Can you help me understand this issue better?

I don't see anywhere in the code where you are attempting to create a 
tablespace ?



Dave Cramer



On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw  wrote:

Hello.

I have searched the net in an attempt to find if others have had
and resolved this challenge, but most of the sites talk about how,
when using the psql, this error arises. In my case, the error
arises only when access PG-15 using JDBC.
JDBC connects to the database, but when trying to execute a
schema, it be to create a database or to create a tabelspace I get
this error :

*StackTrace : [Ljava.lang.StackTraceElement;@7a69b07**
**Message : ERROR: CREATE TABLESPACE cannot run inside a
transaction block*

I have used the same algorithm, but now modify to accommodate
PG-15, when using SQLite. So the JDBC code might not be the
problem, but the requirements needed from PG-15.

Change of perspective, I resolved the concern!

Here is what changed the outcome :
--- snip ---

public void connectToDatabase() throws ClassNotFoundException,
SQLException {
    try {
    Class.forName(this.getDatabaseClass().toString());
    this.conn =
DriverManager.getConnection(this.getDatabaseUrl().toString(),
    this.getUserID().toString(),
    this.getUserPassword().toString());

*this.conn.setAutoCommit(true);*/*//*//*game changer!!*/

        this.pout("Connected to the PostgreSQL server, success!!!");

    this.stmt = this.conn.createStatement();
    } catch (final SQLException | ClassNotFoundException e) {
    throw e;
    }
}

--

All I had to do was to setAutoCommit to true, PG-15 set this value
to false by default.


I hope my experience can help others.

JDBC + PG-15 but not psql

[Amn Ojee Uw]

Hello.

I have searched the net in an attempt to find if others have had and 
resolved this challenge, but most of the sites talk about how, when 
using the psql, this error arises. In my case, the error arises only 
when access PG-15 using JDBC.
JDBC connects to the database, but when trying to execute a schema, it 
be to create a database or to create a tabelspace I get this error :


*StackTrace : [Ljava.lang.StackTraceElement;@7a69b07**
**Message : ERROR: CREATE TABLESPACE cannot run inside a transaction block*

I have used the same algorithm, but now modify to accommodate PG-15, 
when using SQLite. So the JDBC code might not be the problem, but the 
requirements needed from PG-15.


Change of perspective, I resolved the concern!

Here is what changed the outcome :
--- snip ---

public void connectToDatabase() throws ClassNotFoundException, 
SQLException {

    try {
    Class.forName(this.getDatabaseClass().toString());
    this.conn = 
DriverManager.getConnection(this.getDatabaseUrl().toString(),

    this.getUserID().toString(),
    this.getUserPassword().toString());

*this.conn.setAutoCommit(true);*/*//*//*game changer!!*/

        this.pout("Connected to the PostgreSQL server, success!!!");

    this.stmt = this.conn.createStatement();
    } catch (final SQLException | ClassNotFoundException e) {
    throw e;
    }
}

--

All I had to do was to setAutoCommit to true, PG-15 set this value to 
false by default.



I hope my experience can help others.

Re: Creating a TABLESPACE

[Amn Ojee Uw]

Sorry, did not mean it that way.
Thanks for the advice.

On 8/16/23 11:37 a.m., Ron wrote:
Friendly tip: whatever else you do, don't write "Hello children!" in 
English to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.

Re: Creating a TABLESPACE

[Amn Ojee Uw]

Yes!
Thank you Mateusz.

On 8/16/23 6:43 a.m., Mateusz Henicz wrote:

Hey,
You are using "WITH tablespace_option" wrong.

Try it like this:

postgres=# CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION 
'/database' WITH 
(seq_page_cost=1.0,random_page_cost=4.0,effective_io_concurrency=1);

CREATE TABLESPACE

Cheers,
Mateusz

śr., 16 sie 2023 o 12:10 Amn Ojee Uw  napisał(a):

Hello children!

I am learning how to create a tablespace, and AFAIK this are the
steps to follow.

First step is to create the directory where the database files
will be created, for that I am using the root directory
'/database'. This directory is be owned by the postgres group.

Example :
sudo mkdir /database
sudo chown postgres:postgres /database

I read the following documentation
1.) TABLESPACE
<https://www.postgresql.org/docs/current/sql-createtablespace.html>

2.) seq_page_cost

<https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-SEQ-PAGE-COST>

3.) effective_io_concurrency

<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY>

4.) maintenance_io_concurrency

<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY>

5.) Tablespaces
<https://www.postgresql.org/docs/current/manage-ag-tablespaces.html>

x.) effective_io_concurrency
<https://manpages.debian.org/testing/manpages-dev/posix_fadvise.2.en.html>

I then login PostgreSQL as so : sudo -u postgres psql.

Note : _/*jamiil*/_ is an actual account, it does exist!

I proceed to create the 'TABLESPACE' using the default values in
PostgreSQL

CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database'
WITH tablespace_option = seq_page_cost(1.0), random_page_cost
(4.0), effective_io_concurrency (1), maintenance_io_concurrency (10);

But then I get this error message:
*ERROR:  syntax error at or near "tablespace_option"**
**LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH
tablespace...*

What did I go wrong?

This exercise is not imperative, but as a student of PostgreSQL
I'd like to try all there is to learn about PostgreSQL.

Thanks in advance for your time and interest.

Creating a TABLESPACE

[Amn Ojee Uw]

Hello children!

I am learning how to create a tablespace, and AFAIK this are the steps 
to follow.


First step is to create the directory where the database files will be 
created, for that I am using the root directory '/database'. This 
directory is be owned by the postgres group.


Example :
sudo mkdir /database
sudo chown postgres:postgres /database

I read the following documentation
1.) TABLESPACE 



2.) seq_page_cost 



3.) effective_io_concurrency 



4.) maintenance_io_concurrency 



5.) Tablespaces 



x.) effective_io_concurrency 



I then login PostgreSQL as so : sudo -u postgres psql.

Note : _/*jamiil*/_ is an actual account, it does exist!

I proceed to create the 'TABLESPACE' using the default values in PostgreSQL

CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database' WITH 
tablespace_option = seq_page_cost(1.0), random_page_cost (4.0), 
effective_io_concurrency (1), maintenance_io_concurrency (10);


But then I get this error message:
*ERROR:  syntax error at or near "tablespace_option"**
**LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH 
tablespace...*


What did I go wrong?

This exercise is not imperative, but as a student of PostgreSQL I'd like 
to try all there is to learn about PostgreSQL.


Thanks in advance for your time and interest.

Re: A Good Beginner's Book

[Amn Ojee Uw]

Thanks for the advice.
I just ordered it from Amazon.

On 8/12/23 6:59 p.m., Anthony DeBarros wrote:

Hi,

On Sat, Aug 12, 2023 at 6:23 PM Miles Elam  
wrote:


> On 8/12/23 9:02 a.m., Amn Ojee Uw wrote:
>
> Is there a book to be recommended for PostgreSQL beginners?


I’m the author of Practical SQL from No Starch Press. My book combines 
an intro to SQL with lessons on data analysis. Learn more at 
https://practicalsql.com

Re: A Good Beginner's Book

[Amn Ojee Uw]

*Addendum* : I am using JDBC as the framework.

On 8/12/23 9:02 a.m., Amn Ojee Uw wrote:

Hello folks.

Is there a book to be recommended for PostgreSQL beginners?

Thanks in advance.

A Good Beginner's Book

[Amn Ojee Uw]

Hello folks.

Is there a book to be recommended for PostgreSQL beginners?

Thanks in advance.

Re: My 1st TABLESPACE

[Amn Ojee Uw]

Thanks Negora.

Makes sense, I will check it out.

On 8/7/23 1:48 a.m., negora wrote:


Hi:

Although the "postgres" user owns the "data" directory, Has he access 
to the whole branch of directories? Maybe the problem is that he can't 
reach the "data" directory.


Regards.


On 07/08/2023 07:43, Amn Ojee Uw wrote:


I'd like to create a TABLESPACE, so, following this web page 
<https://www.postgresql.org/docs/current/sql-createtablespace.html>,  
I have done the following :


*mkdir 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo chown postgres:postgres 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo -u postgres psql*

*\du**
** arbolone    | Cannot login  | {}**
** chispa | | {prosafe}**
** workerbee | Superuser, Create DB    | {arbolone}**
** jme | | {arbolone}**
** postgres    | Superuser, Create role, Create DB, Replication, 
Bypass RLS | {}**
** prosafe  | Cannot login  | 
{}**


**CREATE TABLESPACE jmetablespace OWNER jme LOCATION 
'/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data';*


The *CREATE **TABLESPACE* schema throws this error message :

/*ERROR:  could not set permissions on directory 
"/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data": 
Permission denied*/


I have followed the web page to the best of my abilities, and AFAIK, 
the postgres user owns the folder '*data*'.


I know that something is missing, where did I go wrong and how can I 
resolve this issue?



Thanks in advance.

My 1st TABLESPACE

[Amn Ojee Uw]

I'd like to create a TABLESPACE, so, following this web page 
,  I 
have done the following :


*mkdir 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo chown postgres:postgres 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo -u postgres psql*

*\du**
** arbolone    | Cannot login  | {}**
** chispa |    | 
{prosafe}**

** workerbee | Superuser, Create DB    | {arbolone}**
** jme |    | 
{arbolone}**
** postgres    | Superuser, Create role, Create DB, Replication, Bypass 
RLS | {}**

** prosafe  | Cannot login  | {}**

**CREATE TABLESPACE jmetablespace OWNER jme LOCATION 
'/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data';*


The *CREATE **TABLESPACE* schema throws this error message :

/*ERROR:  could not set permissions on directory 
"/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data": 
Permission denied*/


I have followed the web page to the best of my abilities, and AFAIK, the 
postgres user owns the folder '*data*'.


I know that something is missing, where did I go wrong and how can I 
resolve this issue?



Thanks in advance.

Re: role "my_account" does not exist

[Amn Ojee Uw]

I came to understand PG a bit better and then understood where I had 
gone wrong. Thanks folks, but please ignore this email.


My most sincere apologies.

On 8/3/23 8:17 p.m., Amn Ojee Uw wrote:


Hello!

Following this web page 
<https://ubiq.co/database-blog/create-user-postgresql/>, I have tried 
to create a new role/user, but to no avail.


After typing the following command : /*createuser --interactive 
--pwprompt*/ I get the following questions:


Enter name of role to add: company_name
Enter password for new role: xxx
Enter it again: xxx
Shall the new role be a superuser? (y/n) y

Than this error is displayed :
*/createuser: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" /**/

/**/failed: FATAL:  role "my_debian_login_account_name" does not exist/*

How/ever, this issue is not really relevant/, per se, since I am able 
to create roles once I log in PostgreSQL-15. Nonetheless, if the 
service exist, why not usu it. If the services exist and is not 
working, could it be that there is a problem that will come apparent 
later on, when unlike now, it would be during a critical moment. Thus 
the question, what can I do to resolve this issue?

Is there something I am missing?


Thanks

role "my_account" does not exist

[Amn Ojee Uw]

Hello!

Following this web page 
, I have tried to 
create a new role/user, but to no avail.


After typing the following command : /*createuser --interactive 
--pwprompt*/ I get the following questions:


Enter name of role to add: company_name
Enter password for new role: xxx
Enter it again: xxx
Shall the new role be a superuser? (y/n) y

Than this error is displayed :
*/createuser: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" /**/

/**/failed: FATAL:  role "my_debian_login_account_name" does not exist/*

How/ever, this issue is not really relevant/, per se, since I am able to 
create roles once I log in PostgreSQL-15. Nonetheless, if the service 
exist, why not usu it. If the services exist and is not working, could 
it be that there is a problem that will come apparent later on, when 
unlike now, it would be during a critical moment. Thus the question, 
what can I do to resolve this issue?

Is there something I am missing?


Thanks

Re: error: connection to server on socket...

[Amn Ojee Uw]

Thank you Adrian. It Worked!!!


On 8/1/23 2:55 p.m., Adrian Klaver wrote:

On 8/1/23 11:42, Amn Ojee Uw wrote:

Ah! Adrean, here is something interesting.

/*pg_lsclusters*/ displays the following information

/*Ver Cluster Port Status    Owner    Data 
directory  Log file*//*
*//*12  main    5432 down,binaries_missing postgres 
/var/lib/postgresql/12/main 
/var/log/postgresql/postgresql-12-main.log*//*
*//*15  main    5433 online    postgres 
/var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log*/


What is version 12 is doing there, I thought that I had completely 
removed it and replaced it with version 15!!


You didn't, read:

https://www.postgresql.org/message-id/20230731125247.puiaz7fjkfvljdwy%40alvherre.pgsql 



and

https://www.postgresql.org/message-id/33f0fc61-2d0f-60bd-e0ed-4cada3c30...@aklaver.com 



The clue is "binaries_missing".

You remove(ed) all the files except the conf files.



Should I remove that log file?


Run:

sudo apt purge postgresql-12

That will take care of removing all the conf files.


To connect you need to specify the correct port, 5433 or do:

sudo vi /etc/postgresql/15/main/postgresql.conf

and change

port = 5433

to

port = 5432

Then restart the server.

You then need to spend some time at the links in this message:

https://www.postgresql.org/message-id/c1ad730a-a84a-ae97-2f23-6891cb06ef26%40aklaver.com 





Regarding /p...@pfortin.com/ assertion, I am working with a _/Debian 
12/_ machine and /*ls -l /tmp/.s.PGSQL.5432*/
displays the following error message : /*ls: cannot access 
'/tmp/.s.PGSQL.5432': No such file or directory*/


As for Tomas Pospisek questions :
* as which user are you doing that (you can see that via `id`)?

    Due to matters of security let us assume that the output is :
    /*id*//*
    *//*uid=1000(my_account) gid=1000(my_account)
groups=1000(my_account),24(cdrom),25(floppy),27(sudo),29(audio),30(dip),44(video),46(plugdev),100(users),*/
/*106(netdev),111(bluetooth),113(lpadmin),116(scanner),122(postgres),1001(tomcat)*/

* in which directory are you executing it  (you can see that via `pwd`)?

    /*/home/*//*my_account*/

* what is the home directory of the postgres user (you can see that 
via `grep postgres /etc/passwd`) /*

*/

    /*~$ grep postgres /etc/passwd*//*
    *//*postgres:x:113:122:PostgreSQL
    administrator,,,:/var/lib/postgresql:/bin/bash*/

* what does `ls -ld /var/run/postgresql/; ls -l 
/var/run/postgresql/.s.PGSQL.5432` show?


    /*ls -ld /var/run/postgresql/*//*
    *//*drwxrwsr-x 2 postgres postgres 100 Jul 31 20:45
    /var/run/postgresql/*/

    /*ls -l /var/run/postgresql/.s.PGSQL.5432*//*
    *//*ls: cannot access '/var/run/postgresql/.s.PGSQL.5432': No such
    file or directory*/


- what does `journalctl -xeu postgresql` say?

    /*sudo journalctl -xeu postgresql*//*
    *//*[sudo] password for *//*/**//*my_account*/: *//*
    *//*░░ Support: https://www.debian.org/support*//*
    *//*░░ *//*
    *//*░░ A start job for unit postgresql.service has begun 
execution.*//*

    *//*░░ *//*
    *//*░░ The job identifier is 3496.*//*
    *//*Jul 31 20:45:13 *//*tree.com systemd[1]: Finished
    postgresql.service - PostgreSQL R>*//*
    *//*░░ Subject: A start job for unit postgresql.service has finished
    successfully*//*
    *//*░░ Defined-By: systemd*//*
    *//*░░ Support: https://www.debian.org/support*//*
    *//*░░ *//*
    *//*░░ A start job for unit postgresql.service has finished
    successfully.*//*
    *//*░░ *//*
    *//*░░ The job identifier is 3496.*//*
    *//*lines 4-16/16 (END)*/

- what does `tail -n 20 
/var/log/postgresql/postgresql-*-qgisclouddb.log` show?


    /*sudo tail -n 20 
/var/log/postgresql/postgresql-*-qgisclouddb.log*//*

    *//*tail: cannot open
    '/var/log/postgresql/postgresql-*-qgisclouddb.log' for reading: No
    such file or directory*/


I hope I was able to answer all the interesting questions posted as 
reply to my original post.


/Thanks folks, I really appreciate the time you have dedicated to my 
question./



On 8/1/23 10:35 a.m., Adrian Klaver wrote:

On 8/1/23 03:22, Amn Ojee Uw wrote:

Using the following command on my Debian 12 machine:

/*sudo apt-get install postgresql postgresql-15 
postgresql-client-common postgresql-common postgresql-contrib 
postgresql-doc phppgadmin

*/


and following the instruction on this 
<https://wiki.debian.org/PostgreSql#User_access> web page I have 
installed PostgreSQL-15.

The installation went smooth, until the following command was issued :

/*sudo -u postgres psql*/

... I get an error message that reads:

/*could not change directory to "/home/my_account": Permission 
denied*//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: No such file or 
directory*//*
*//*    Is the server running locally and accepting connections on 
that socket?*/


What can I do to resolve this issue?

Re: error: connection to server on socket...

[Amn Ojee Uw]

Ah! Adrean, here is something interesting.

/*pg_lsclusters*/ displays the following information

/*Ver Cluster Port Status    Owner    Data 
directory  Log file*//*
*//*12  main    5432 down,binaries_missing postgres 
/var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log*//*
*//*15  main    5433 online    postgres 
/var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log*/


What is version 12 is doing there, I thought that I had completely 
removed it and replaced it with version 15!!

Should I remove that log file?

Regarding /p...@pfortin.com/ assertion, I am working with a _/Debian 12/_ 
machine and /*ls -l /tmp/.s.PGSQL.5432*/
displays the following error message : /*ls: cannot access 
'/tmp/.s.PGSQL.5432': No such file or directory*/


As for Tomas Pospisek questions :
* as which user are you doing that (you can see that via `id`)?

   Due to matters of security let us assume that the output is :
   /*id*//*
   *//*uid=1000(my_account) gid=1000(my_account)
   
groups=1000(my_account),24(cdrom),25(floppy),27(sudo),29(audio),30(dip),44(video),46(plugdev),100(users),*/
   
/*106(netdev),111(bluetooth),113(lpadmin),116(scanner),122(postgres),1001(tomcat)*/

* in which directory are you executing it  (you can see that via `pwd`)?

   /*/home/*//*my_account*/

* what is the home directory of the postgres user (you can see that via 
`grep postgres /etc/passwd`) /*

*/

   /*~$ grep postgres /etc/passwd*//*
   *//*postgres:x:113:122:PostgreSQL
   administrator,,,:/var/lib/postgresql:/bin/bash*/

* what does `ls -ld /var/run/postgresql/; ls -l 
/var/run/postgresql/.s.PGSQL.5432` show?


   /*ls -ld /var/run/postgresql/*//*
   *//*drwxrwsr-x 2 postgres postgres 100 Jul 31 20:45
   /var/run/postgresql/*/

   /*ls -l /var/run/postgresql/.s.PGSQL.5432*//*
   *//*ls: cannot access '/var/run/postgresql/.s.PGSQL.5432': No such
   file or directory*/


- what does `journalctl -xeu postgresql` say?

   /*sudo journalctl -xeu postgresql*//*
   *//*[sudo] password for *//*/**//*my_account*/: *//*
   *//*░░ Support: https://www.debian.org/support*//*
   *//*░░ *//*
   *//*░░ A start job for unit postgresql.service has begun execution.*//*
   *//*░░ *//*
   *//*░░ The job identifier is 3496.*//*
   *//*Jul 31 20:45:13 *//*tree.com systemd[1]: Finished
   postgresql.service - PostgreSQL R>*//*
   *//*░░ Subject: A start job for unit postgresql.service has finished
   successfully*//*
   *//*░░ Defined-By: systemd*//*
   *//*░░ Support: https://www.debian.org/support*//*
   *//*░░ *//*
   *//*░░ A start job for unit postgresql.service has finished
   successfully.*//*
   *//*░░ *//*
   *//*░░ The job identifier is 3496.*//*
   *//*lines 4-16/16 (END)*/

- what does `tail -n 20 
/var/log/postgresql/postgresql-*-qgisclouddb.log` show?


   /*sudo tail -n 20 /var/log/postgresql/postgresql-*-qgisclouddb.log*//*
   *//*tail: cannot open
   '/var/log/postgresql/postgresql-*-qgisclouddb.log' for reading: No
   such file or directory*/


I hope I was able to answer all the interesting questions posted as 
reply to my original post.


/Thanks folks, I really appreciate the time you have dedicated to my 
question./



On 8/1/23 10:35 a.m., Adrian Klaver wrote:

On 8/1/23 03:22, Amn Ojee Uw wrote:

Using the following command on my Debian 12 machine:

/*sudo apt-get install postgresql postgresql-15 
postgresql-client-common postgresql-common postgresql-contrib 
postgresql-doc phppgadmin

*/


and following the instruction on this 
<https://wiki.debian.org/PostgreSql#User_access> web page I have 
installed PostgreSQL-15.

The installation went smooth, until the following command was issued :

/*sudo -u postgres psql*/

... I get an error message that reads:

/*could not change directory to "/home/my_account": Permission 
denied*//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: No such file or 
directory*//*
*//*    Is the server running locally and accepting connections on 
that socket?*/


What can I do to resolve this issue?


Run

pg_lsclusters

and see what port the server is running on.



Thanks in advance.

Re: error: connection to server on socket...

[Amn Ojee Uw]

_*Addendum*_

I tried this, but to no avail : /*sudo systemctl start postgresql*/

On 8/1/23 6:22 a.m., Amn Ojee Uw wrote:


Using the following command on my Debian 12 machine:

/*sudo apt-get install postgresql postgresql-15 
postgresql-client-common postgresql-common postgresql-contrib 
postgresql-doc phppgadmin

*/


and following the instruction on this 
<https://wiki.debian.org/PostgreSql#User_access> web page I have 
installed PostgreSQL-15.

The installation went smooth, until the following command was issued :

/*sudo -u postgres psql*/

... I get an error message that reads:

/*could not change directory to "/home/my_account": Permission denied*//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory*//*
*//*    Is the server running locally and accepting connections on 
that socket?*/


What can I do to resolve this issue?

Thanks in advance.

error: connection to server on socket...

[Amn Ojee Uw]

Using the following command on my Debian 12 machine:

/*sudo apt-get install postgresql postgresql-15 postgresql-client-common 
postgresql-common postgresql-contrib postgresql-doc phppgadmin

*/


and following the instruction on this 
 web page I have 
installed PostgreSQL-15.

The installation went smooth, until the following command was issued :

/*sudo -u postgres psql*/

... I get an error message that reads:

/*could not change directory to "/home/my_account": Permission denied*//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory*//*
*//*    Is the server running locally and accepting connections on that 
socket?*/


What can I do to resolve this issue?

Thanks in advance.

Completely Removing PostgreSQL

[Amn Ojee Uw]

In my Debian 12, I have removed the following apps from my system by 
using the following commands:


/*sudo apt-*//*get*//*–purge *//*remove*//*postgresql 
postgresql*//*-15*//*postgresql-client-common postgresql-common 
postgresql-contrib*//**//*sudo apt-*//*get*//*–purge *//*remove*//*postgresql 
postgresql*//*-12*//*postgresql-client-common postgresql-common 
postgresql-contrib*/

and then used*sudo**apt autoremove* to remove whatever was left dangling 
there. However, after using *dpkg -l | grep postgres* command, I get this?


**

*dpkg -l | grep postgres*
rc  postgresql-12 12.15-1.pgdg120+1  amd64    The 
World's Most Advanced Open Source Relational Database
rc  postgresql-15 15.3-1.pgdg120+1   amd64    The 
World's Most Advanced Open Source Relational Database
rc  postgresql-client-common 250.pgdg120+1  
all  manager for multiple PostgreSQL client versions
rc  postgresql-common 250.pgdg120+1  all  
PostgreSQL database-cluster manager
rc  postgresql-pljava-common 1.6.4-2.pgdg120+1  
all  Java procedural language for PostgreSQL, config files


-

The above clearly shows that *dpkg*, and therefore my Debian 12, still 
recognize the existence of these packages. When trying to remove them 
*apt remove,* it fails to find them.


How can I completely remove PostgreSQL-xx.xx from my system?


Thanks!

Re: Upgrading

[Amn Ojee Uw]

Thank you Ron and Peter for taking the time to respond to my call for help.

In summery, I'd like to use PostgreSQL-15.

However, since my Debian 12 already has already PostgreSQL-15 installed, 
did I inadvertently overwrote PostgreSQL-15 when I installed 
PostgreSQL-12? If so, how do I remove PostgreSQL-12 and continue on with 
PostgreSQL-15.


Please note that I installed using the following command

/*sudo apt-get -y install postgresql-12*/

Thanks in advance.


On 7/30/23 11:34 a.m., Peter J. Holzer wrote:

On 2023-07-30 07:53:54 -0400, Amn Ojee Uw wrote:

On my Debian 12, I installed PostgreSQL-12,

Where did you install that from?

AFAICS, Debian 12 comes with PostgreSQL 15.


I'd like to upgrade to the latest release of PostgreSQL. So, my
question is, what is the best way to upgrade to the next release of
PostgreSQL?

If you stay with the same source, Just installing the new version and
then invoking pg_upgrade (or a variant - PGDG, Debian, Ubuntu have
pg_upgradecluster) should do the trick.

If you switch sources, the setup may be sufficiently different that
pg_dump/pg_restore may be the easiest way.

 hp

Upgrading

[Amn Ojee Uw]

Just a quick question.

On my Debian 12, I installed PostgreSQL-12, I'd like to upgrade to the 
latest release of PostgreSQL. So, my question is, what is the best way 
to upgrade to the next release of PostgreSQL?


Thanks in advance.

Re: PostgreSQL - How to login with my Linux user account

[Amn Ojee Uw]

First of all, my gratitude to Adrian and David for taking the time to 
reply to my call for help; a 1k  thanks to you kids.


Having stated the above, PostgreSQL provides a default database namely 
'postgres'. Taking advantage of this service, I have change the 'psql' 
statement to 'psql -d postgres -U my_linux_user_name -W', I then entered 
my Linux account login password and got this error messages :

/*$ psql -d postgres -U my_linux_user_name -W *//*
*//*Password: *//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_linux_user_name"*/


I also, using 'postres' account, have created another user account, 
chispa, who is a superuser; when trying to login as 'chispa' and using 
the above statement (/*psql -d postgres -U chispa -W *//*)*/ PostgreSQL 
throws the same error message.

/**/

What am I doing wrong?
Is there a webpage that explains how to login to PostgreSQL as other 
than 'postgres'?



Thanks in advance.

On 7/27/23 8:19 p.m., Amn Ojee Uw wrote:

psql -d mydb -U myuser -W

PostgreSQL - How to login with my Linux user account

[Amn Ojee Uw]

I'd like to log into my newly installed PostgreSQL [ PostgreSQL 12.15 
(Debian 12.15-1.pgdg120+1) ] using my linux account.
In trying to do that this web page 
suggest 
that I enter :


psql -d mydb -U myuser -W

However, that command requires a database_name (mydb), and PosgreSQL 12.15 
being so freshly installed does not yet
have any database created by me.

How would you suggest I circumvent this challenge?

Mind you that, using the postgres account I have created other accounts.


Role name | Attributes | Member 
of
---++---
 chispa| Superuser  | {}
 pedro | Superuser  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


Thanks in advance.

Re: Nu-B here

[Amn Ojee Uw]

Please note the following :
/psql (12.15 (Debian 12.15-1.pgdg120+1
/Thanks in advance
//

On 7/20/23 10:41 a.m., Adrian Klaver wrote:

On 7/19/23 23:32, Alban Hertroys wrote:



On 20 Jul 2023, at 02:36, Amn Ojee Uw  wrote:

After this command 'sudo -u postgres psql'
I get this message :
could not change directory to "/home/my_account": Permission denied


What’s the reason that you’re using the OS user postgres?


Because the Debian/Ubuntu packaging sets up pg_hba.conf to use peer 
auth for local connections and the only database user created is 
postgres.


See:

https://ubuntu.com/server/docs/databases-postgresql

for more information.



If you’re simply trying to connect to the database named postgres as 
database user postgres, you can instead use the command 'psql -U 
postgres postgres’.





Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

My 1st JDBC and PostgreSQL

[Amn Ojee Uw]

Following the example in :
https://www.tutorialspoint.com/postgresql/postgresql_java.htm
I wrote the bellow method :

public void connectToDatabase() throws ClassNotFoundException, 
SQLException {

    try {
    this.perr(this.getDatabaseClass()); // 
org.postgresql.Driver = Class
    this.perr(this.getDatabaseUrl());   // 
jdbc:postgresql://localhost:5432/sara.pgdb = url

    this.perr(this.getUserID());    // chispa = user
    this.perr(this.getUserPassword());  // 8UUKZW = password

    Class.forName(this.getDatabaseClass().toString());
    conn = 
DriverManager.getConnection(this.getDatabaseUrl().toString(),

    this.getUserID().toString(),
    this.getUserPassword().toString() );
    } catch (final SQLException | ClassNotFoundException e) {
    throw e;
    }
    }

However, it throws an exception that reads :
Exception in thread "main" org.postgresql.util.PSQLException: FATAL: 
database "sara.pgdb" does not exist


In reference to the above web page, it reads :
The following Java code shows how to connect to an existing database. If 
the database does not exist, then it will be created and finally a 
database object will be returned.


True, the database file does not exist, but isn't JDBC supposed to 
create it?



Any help would be much appreciated.

Nu-B here

[Amn Ojee Uw]

After this command 'sudo -u postgres psql'
I get this message :
*could not change directory to "/home/my_account": Permission denied**
**psql (12.15 (Debian 12.15-1.pgdg120+1))**
**Type "help" for help.*

I tried using help, but to no avail.
What am I doing wrong?

Thanks in advance!