Tag Archives: PostgreSQL

[Solved] Gitlab Error: gitlab-ctl reconfigure Error: STDERR: initdb: error: invalid locale settings; check LANG and L

The error report is shown in the figure

There was an error running gitlab-ctl reconfigure:

execute[/opt/gitlab/embedded/bin/initdb -D /var/opt/gitlab/postgresql/data -E UTF8] (postgresql::enable line 49) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of /opt/gitlab/embedded/bin/initdb -D /var/opt/gitlab/postgresql/data -E UTF8 ----
STDOUT: The files belonging to this database system will be owned by user "gitlab-psql".
This user must also own the server process.
STDERR: initdb: error: invalid locale settings; check LANG and LC_* environment variables
---- End output of /opt/gitlab/embedded/bin/initdb -D /var/opt/gitlab/postgresql/data -E UTF8 ----
Ran /opt/gitlab/embedded/bin/initdb -D /var/opt/gitlab/postgresql/data -E UTF8 returned 1
                   Default character set issues

Ensure that there is more than 4G in the

Solution process:

vim /etc/profile
# Add the following to the end of the configuration file
export LC_CTYPE=en_US.UTF-8
export LC_ALL=en_US.UTF-8
# reread the environment variables
[root@localhost src]# source /etc/profile
# Reload
[root@localhost src]# gitlab-ctl reconfigure
It is best to change the password the first time you enter
Details: > https://blog.csdn.net/weixin_46115601/article/details/120721246

Set the maximum number of Postgres connections Error [How to Solve]

The Postgres version of this tutorial case is 12

Problem Description:

psycopg2.OperationalError: FATAL: sorry, too many clients already on

In the morning, I found that the python back-end service I maintained reported an error. Baidu found that the default maximum concurrency of Postgres was only 100, which led to this problem

Modify tutorial:

cd /var/lib/postgresql/12/main
vi postgresql.auto.conf

Add the following line of code to the file to set the maximum number of connections to 800, the default is 100
max_connections = 800

Restart the service
service postgresql restart

The following is the SQL command to check whether the modification is successful:

# Show the maximum number of connections to the current database
show max_connections;

# Show the number of users currently connected to the data
SELECT COUNT(*) from pg_stat_activity;

# Show details of the current connection, very slow, don't use it
SELECT * FROM pg_stat_activity;

The effect is similar, which indicates that the setting is successful

[Solved] PostgreSQL Remote Access Error: could not connect to server

1. Turn off the postgresql service first
. 2. If you want postgresql to support remote access, you need to modify the two configuration files in the data folder under the directory

postgresql.conf
Set the value of the listen_addresses item in the file to ‘*’
pg_hba.conf
Add the following configuration under the host all all 127.0.0.1/32 md5 line after the ipv4 configuration of the configuration file, or directly modify this line to the following configuration host all 0.0.0.0/0 trust
If you do not want to allow all IP remote access, You can set 0.0.0.0 in the above configuration items to a specific IP value.
3. Right-click management on my computer-find service-find postgresql service, right-click to start

[Solved] PostgreSQL enumeration type usage error: operator does not exist error handling 

//Creating Enumeration Classes
CREATE TYPE USER_ROLE AS ENUM ('MALE', 'FEMALE');
//Add conversion rules
CREATE CAST (VARCHAR AS USER_ROLE) WITH INOUT AS IMPLICIT;
//Create table, add fields of enumeration type
create table sys_user
(
    row_id      bigserial          not null
        constraint sys_user_pkey primary key,
    create_time timestamp(6),
    update_time timestamp(6),
    del_flag    smallint default 0 not null,
    role        USER_ROLE      not null,
    user_name   varchar(200)       not null
);

Because the conversion rule is added, you can directly use the varchar type string as the judgment condition query in pgadmin, but if you use mybatis to query the database, the error operator does not exist will be reported

select * from sys_user where del_flag = 0 and role = 'MALE'

Solution: convert varchar type to enumeration type and compare

Method 1
select * from sys_user where del_flag = 0 and role = cast(#{role} as user_role)；
Method 2
select * from sys_user where del_flag = 0 and role = #{role}::user_role;

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying XXX

PG database SQL error:
error reason: is caused by SQL writing error or parameter transfer error
database error information

[42883] ERROR: operator does not exist: character varying = record 
No operator matches the given name and argument types. You might need to add explicit type casts. 
line: 1063

error example: 

select * from d_allotmoveai_setarea a where a.bills_id = ('123','456')

correct example: 

select * from d_allotmoveai_setarea a where a.bills_id = ('456')
select * from d_allotmoveai_setarea a where a.bills_id in ('123','456')

ERROR Message:
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying <> record No operator matches the given name and argument types. You might need to add explicit type casts. line: 323 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159) ~[postgresql-42.2.11.jar:42.2.11] at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109) ~[postgresql-42.2.11.jar:42.2.11] at com.p6spy.engine.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78) ~[p6spy-3.9.1.jar:na] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.2.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]

Used by: Java. Lang. classnotfoundexception: org. PostgreSQL. Driver

Always report an error saying that failed by: Java. Lang. classnotfoundexception: org. PostgreSQL. Driver

After a series of searching to find out the reasons, such as the quotation in the box is wrong, I can’t quote what I need;

Only the writing method as shown in the figure below can be quoted successfully,

In memory of the time I’ve been tossing about, it’s also a small lesson of using gradlew for the first time

postgresql FATAL：no pg_hba.conf entry for host “192….“

In PG_ Add the IP to hbaconf and restart postgreql
as follows:[ https://blog.csdn.net/qq_ 36434219/article/details/118277681]

Note: there are many IP addresses added, one by one… You can directly configure the fixed network segment. For example, the above figure can be configured as follows:
192.168.3.0/24 represents having 254 IP addresses: from 192.168.1.1 to 192.168.1.254

postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a

ERROR: column “t.domainid” must appear in the GROUP BY clause or be used in an aggregate function

The data table is as follows.

 select * from td_link_1h_d_20210427 limit 10;
 
    time    | linkid | domainid | ipversion |    upbps    |    dnbps    | updisbps | dndisbps  |  upmaxbps   |  dnmaxbps   |  upminbps   |  dnminbps   
------------+--------+----------+-----------+-------------+-------------+----------+-----------+-------------+-------------+-------------+-------------
 1619452800 |   4401 |    10001 |         4 |   147856388 |     5923764 |        0 |         0 |   166378265 |    12533696 |   123212832 |     2247349
 1619452800 |   5002 |    10001 |         6 |           0 |           0 |        0 |         0 |           4 |           4 |           0 |           0
 1619452800 |   5503 |    10001 |         6 |           0 |         149 |        0 |         0 |           2 |         194 |           0 |         102
 1619452800 |   8724 |    10001 |         6 |         238 |           0 |        0 |         0 |         609 |           2 |          23 |           0
 1619452800 |   1201 |    10001 |         4 |   255570975 |  4111481619 |  3035634 |  19703184 |   292787968 |  4751690795 |   209619623 |  3334699801
 1619452800 |   4700 |    10001 |         4 | 19626007220 |  8697825644 |        0 |         0 | 23504765993 |  9930189669 | 16586678202 |  7205462505
 1619452800 |  13249 |    10001 |         6 |     3066257 |      262560 |        0 |        12 |     5817708 |      339458 |      954397 |      217159
 1619452800 |   9156 |    10001 |         4 | 19773923700 |  2984265593 |        0 |         0 | 22335976464 |  3388045463 | 17124116138 |  2543622015
 1619452800 |  11741 |    10001 |         4 |  5628620627 | 20052950343 | 51741713 | 318854796 |  6958633624 | 23485369390 |  4357751762 | 15861228522
 1619452800 |   2700 |    10001 |         4 |   657502770 |  2246692965 |        0 |         0 |   845823645 |  2394743374 |   506699006 |  1880772827
(10 rows)

The following error occurred when querying the database by grouping statistics:

select linkid as "ID",domainid as "Domain",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "Time",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time order by linkid,time;

ERROR:  column "t.domainid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select linkid as "ID",domainid as "Domain",to_char(time::abs...
                                   ^

After checking a lot of data, it is found that the error means that the domainid field must appear in group by or be used for aggregation function

so I modify the SQL statement, add the domainid field to group by, and then query again

 select linkid as "ID",domainid as "Doamin",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "TIME",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time,domainid order by linkid,time,domainid;

Error in PostgreSQL installation of win10

problem

The installation of PostgreSQL in win10 reports an error. The 10, 11 and 12 versions downloaded from the official website are not available. The error is as follows

there has been an error. Error running icacls
"c:\Users\abdo\AppData\Local\Temp/postgresql_installer_b4be1776ca"/T/Q/grant: Access is denied


How to fix
install this version https://get.enterprisedb.com/postgresql/postgresql-11.2-1-windows-x64.exe

https://superuser.com/questions/1475822/postgresql-install-fail-in-windows-10-icacls-error-access-denied

pg_ctl: no database directory specified and environment variable PGDATA unset ， centos 7 postgreSQL

centos 7 postgreSQL pg_ CTL invalid

In the
section

~/.bash_profile

Next configuration

export PGDATA=/var/lib/pgsql/11.0/data

But it didn’t work.

However, it can be written like this

Go to 

/usr/pgsql/bin

After that, it can be executed

./pg_ctl -D /var/lib/pgsql/11.0/data start

Take notes