Tag Archives: PostgreSQL

EF core Creates pg library and Model Report an Error [Solved] 

PM> Scaffold-DbContext "server=localhost;database=SimulationDatabase;uid=postgres;pwd=123456;port=5432;" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir PgModel -Force
Build started...
Build succeeded.
To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
Npgsql.PostgresException (0x80004005): 3D000: ���ݿ� "SimulationDatabase" ������
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|195_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
   at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.<Get>g__RentAsync|28_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.Open()
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(DbConnection dbConnection, DatabaseModelFactoryOptions options)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ��������
    SqlState: 3D000
    MessageText: ���ݿ� "SimulationDatabase" ������
    File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\init\postinit.c
    Line: 879
    Routine: InitPostgres
3D000: ���ݿ� "SimulationDatabase" ������

 

Solution:

PM> Scaffold-DbContext "Host=localhost; Port=5432 ;Database=simulation;Username=postgres;Password=123456" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir PostgreSqlModels -Context DBContext -force
Build started...
Build succeeded.
To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.

[Solved] error: password authentication failed for user “postgres”

password authentication failed for user “postgres” with docker

Steps:

    1. 1. run the instruction to create a docker container.

docker run --rm --name test-postgres -p 5432:5432 -e POSTGRES_PASSWORD=pw -d postgres

    1. 2. run the following command in node code to connect to the database:
import pg from 'pg'
const { Pool } = pg
pool = new Pool({
   database: 'postgres',
   user: 'postgres',
   password: 'pw',
   port: 5432
})

The following error is thrown:
error: password authentication failed for user “postgres”

 

Cause analysis

Because Postgres has been installed locally, Postgres is automatically started when the system is started by default. When connecting to the database, the locally installed Postgres service is preferentially connected, so the connection fails.

 

Solution:

Open Window Task Manager, under Services you will see a postgres service running. Right-click to turn off the postgres service, open the Service window and double-click on the postgres service to set the Startup Type to Manual.
then


Other troubleshooting methods

Open the Terminal of the container

  2. Enter the following two commands to see if there is a problem with local host permissions.
    cd var/lib/postgresql/data
    cat pg_hba.conf
  3. To see if the default user is postgres.
    psql -U postgres -x -c "select * from current_user;"
  4. Check the password expiration date. rolvaliduntil no value means no expiration date.
    psql -h 127.0.0.1 -U postgres -d postgres
    SELECT * FROM pg_roles WHERE rolname='postgres';
  5. Try clearing docker’s columns and containers, and restarting docker

[Solved] Sharding-Proxy Insert Datas Error: Sharding value must implements Comparable NoSuchElementException

data insertion error
1. After doing sub-database and sub-table, it is now necessary to migrate the existing data of a single database to the corresponding sub-database and sub-table database
2. Data migration is to read the table structure information of the specified table, use 3. PreparedStatement, and use placeholders to splicing and submitting data
3. In order to prevent migration errors, migrate multiple times, clear the sharding table each time, and then migrate
4. During the migration, some errors were encountered, version 5.0

Report an error

Sharding value must implements Comparable

CommandExecutorTask - Exception occur: 
java.lang.IllegalArgumentException: Sharding value must implements Comparable.

Causes and Solutions

1. A non-null field with a null value inserted
2. It may be the primary key. If the primary key policy is not set and it is not automatically generated, an error will be reported.
3. It may be the sub-database sub-table key, which cannot be null. This is my situation, the data is maintained, and the sub-database fields of all tables are assigned corresponding values, and the error is resolved

Error report 2

NoSuchElementException

org.postgresql.util.PSQLException: ERROR: java.util.NoSuchElementException

Causes and Solutions

1. The reason is that the number of inserted values ​​does not match the number of table fields.
2. The first time I encountered this error, it was due to the use of sub-database sub-table and data encryption at the same time
3. The field configuration of data encryption affects the calculation and reading of the number of fields, resulting in the mismatch between fields and inserted values, resulting in an error. 4.  After temporarily dropping the data encryption,Solved
4. The second time I encountered this error, I found that the structure of the original table was changed, and 2 fields were added. The table structure of the sub-database and sub-tables has not changed, resulting in the field value and quantity not corresponding to each other. Maintain the table structure of the sub-database and sub-tables After that, solve it (the problem is that after adding the field, it will not take effect without restarting sharding-proxy)

Ansible Use yum module to install rpm Error: Request failed: ＜urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate v

Project scenario:

Today I used Ansible to install postgresql-13 and wrote the following test script from the official installation tutorial.

- hosts: postgresql
  gather_facts: false
  tasks:
    - name: install pg repo
      yum:
        name: https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
        state: present
    - name: install pg
      yum:
        name: postgresql13-server
        state: present
    - name:
      stat:
        path: "/var/lib/pgsql/data/pg_hba.conf"
      register: postgres_data
    - name:
      shell: "/usr/pgsql-13/bin/postgresql-13-setup initdb"
      when: not postgres_data.stat.exists
    - name:
      systemd:
        name: postgresql-13
        enabled: yes
        daemon_reload: yes
        state: started

Problem Description:

the following error occurs when running the script:

fatal: [172.17.200.2]: FAILED! => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "msg": "Failure downloading https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm, Request failed: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:618)>"}

Solution:

Just modify https to http in https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[Solved] PostgreSQL configure: error: readline library not found

preface

An error occurs when installing PostgreSQL, as shown below

configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.

Solution:

Check whether the readLine package is installed in the system

rpm -qa | grep readline

Install readline-devel pack

yum -y install -y readline-devel

Execute configure again successfully

The explanation of readLine comes from the official website

--without-readline
Prevents use of the Readline library (and libedit as well). Thisoption disables command-line
editing and history in psql, so it is notrecommended.

Note: when you execute configure, you can add “– without readLine” to avoid this error, but PostgreSQL officials do not recommend it

[BUG-mybatis-plus] org.postgresql.util.PSQLException: ERROR: syntax error at or near “current_date“

Environmental Science:

         spring cloud   + Mybatis-plus + postgresql

Scenario:

  Prompt during table insertion

         org.postgresql.util.PSQLException: ERROR: syntax error at or near “current_date”

  Causes:

         current_ Date() is the method of SQL, current_ Date is the method name

resolvent:

         current_ Change date to create_ Date (optional here, not the method name)

Analysis process:

         The Error statement in XML file and navigator displays current_ Date is green (keyword color)

 

 

[PostgreSQL tutorial] · PostgreSQL error prompt: “error 42501” or “permission denied”

Article catalog

1. Problem overview 2. Solution

1. Problem overview

log in to the PostgreSQL database service with the PSQL command. When querying data, you receive an error log prompt, as follows: 

“Error 42501” or “Permission Denied”

2. Solution

PostgreSQL error code 42501 is a common error, which sometimes occurs when responding to PostgreSQL database queries. In most cases, the error code 42501 indicates that the user has insufficient permissions on the database. PostgreSQL responds with an error message whenever a user with insufficient permissions makes a query

to solve this problem, please check the database user permissions. If the user trying to query lacks permission, just change the permission accordingly. You can use “ grant select on table_name to public; ” to grant permission to the public. Or use the command “ grant select on table_name to user_name; ” for only a few users

[PostgreSQL tutorial] · out of memory issue

Article catalog

1. Problem description 2. Solution

1. Problem description

the PostgreSQL server running on the server receives an error log prompt message: 

ERROR: out of memory 2021-11-24 
DETAIL: Failed on request of size 1880.

2. Solution

the memory of each system is limited. When there is no memory left, PostgreSQL’s memory allocation function begins to fail. Please check your hardware configuration and limitations

Solve the problem of postgre error #22p02 malformed array literal

catalogue

Background

Problem solving

Background

When you use the go PG framework to modify a column, an error occurs.

The column to be modified is bigint [] type. Keep trying, and multiple errors are reported as follows:

Missing   “=”   after   array   dimensions

ERROR #22P02 malformed array literal

Problem solving

Modify data directly:

update table_name set  xxx= '{1,2}' where id=2;

  This modification is successful. Is it right to contact the code and convert the value types to be injected?Yes, that’s it!

Code mode: set (“column =?”, Val)   

Convert each value of Val of type [] Int64 into a string, and concatenate the string with commas: ` {% s}`

Expand to see: set (“column =?”, ` {1,2} ‘)   

Done!