This is the second day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021 “.
Npgsql is the.net version of postgreSQL connection library, when we use.NET for postgreSQL development, the first contact is Npgsql, if interested, can see how to write the source code.
- 📢 welcome to like: 👍 collect ⭐ message 📝 if there are mistakes please correct, give people rose, hand left lingering fragrance!
- 📢 This article was originally published by Webmote and published by CSDN.
- 📢 author’s motto: life is toss about, when you don’t toss about life, life will start to toss about you, let us come on together! 💪 💪 💪
1 Connection String
The connection string of the condensed version of PostgreSQL is as follows:
"Host=xxxx; Port=5432; Database=postgres; Username=postgres; Password=xxxxx;"Copy the codeThe format of the connection string is keyword1=value; keyword2=value; Key is case insensitive. Each group of keyvalues is separated by a semicolon (;).
Note: The link library implemented by Npgsql supports only the keywords listed in the following sections.
2 Basic Parameters
| The scope of | describe | The default |
|---|---|---|
| Host | Specify the host name for running PostgreSQL. Multiple hosts can be specified, (comma separates multiple hosts). If the value begins with a slash, use it as the directory for Unix domain sockets | The necessary |
| Port | TCP port of the PostgreSQL service. | Default: 5432 |
| Database | The PostgreSQL database to connect to. | Default: Same as the user name |
| Username | The user name to connect to. This is not required if IntegratedSecurity is used. | PGUSER |
| Password | Password to connect. This is not required if IntegratedSecurity is used. | PGPASSWORD |
| Passfile | PostgreSQL Path to the password file (PGPASSFILE) from which to obtain the password. | PGPASS file |
3. Security and encryption parameters
| parameter | describe | The default |
|---|---|---|
| SSL Mode | Controls whether SSL is enabled, | 6.0 + recommend |
| Trust Server Certificate | Whether to trust the service certificate without validating it. | false |
| Client Certificate | Path of the client certificate | PGSSLCERT |
| Client Certificate Key | Certificate of the Key | PGSSLKEY |
| Root Certificate | Verify the root certificate path of the server certificate | PGSSLROOTCERT |
| Check Certificate Revocation | Check whether the certificate is revoked after authorization | 6.0 + to true |
| Integrated Security | Whether to mix authentication (GSS/SSPI). | false |
| Persist Security Info | Whether to set the connection password as sensitive information, if so, the password is not returned when the connection string is obtained | false |
| Kerberos Service Name | Kerberos Server Name | postgres |
| Include Realm | Kerberos Realm for authorization to use | |
| Include Error Detail | When activated, PostgreSQL error and notification details are included in PostgresException.Detail and postgresnotice.detail. | false |
| Log Parameters | If enabled, the parameter values in the command are logged | false |
4. Link pool parameters
Important Chapters:
| parameter | describe | The default |
|---|---|---|
| Pooling | Whether to activate the link pool. | True: Activated by default |
| Minimum Pool Size | Minimum number of link pools. | 0 |
| Maximum Pool Size | Maximum number of link pools. | Version 3.1+ is 100, and the previous version was 20 |
| Connection Idle Lifetime | Unit: second. The time to wait before closing idle links in the pool if the link count exceeds the minimum link number limit | 300 |
| Connection Pruning Interval | Link pruning interval (how long it takes to process idle links in the pool) | 10 |
| ConnectionLifetime | How long is the lifetime of the link, as determined when it is returned from the pool, and if it is exceeded, the link is closed and destroyed. This is very effective for failover and load balancing | 0 (disabled) |
5 Timeout and heartbeat hold parameters
Important chapters:
| parameter | describe | The default |
|---|---|---|
| Timeout | In seconds, the timeout period for establishing a link | 15 |
| Command Timeout | The unit is second. Timeout period for executing the link command. If this parameter is set to 0, it indicates that the link does not time out | 30 |
| Internal Command Timeout | Specifies the timeout period for executing an internal command. -1 uses CommandTimeout and 0 does not timeout. | – 1 |
| Cancellation Timeout | Cancels the timeout period for a query. -1 skips the wait and 0 waits indefinitely. | 2000 |
| Keepalive | Heartbeat time, keepalive link. | 0 (disabled) |
| Tcp Keepalive | Whether to use the default TCP Keepalive as the heartbeat parameter | false |
| Tcp Keepalive Time | TCP keepalive time, in milliseconds. Supports Windows only. | 0 (disabled) |
| Tcp Keepalive Interval | The unit is millisecond. If no ACK is received, the interval for consecutive heartbeat packets supports Windows only. | Tcp Keepalive Time 值 |
6 Performance Parameters
| parameter | describe | The default value |
|---|---|---|
| Max Auto Prepare | The maximum number of SQL statements that can be automatically prepared at any given point. Above this number, the least recently used statement is reclaimed. 0 Disable automatic preparation. | 0 |
| Auto Prepare Min Usages | The minimum number of times an SQL statement is used before it is automatically prepared. | 5 |
| Use Perf Counters | Cause Npgsql to write performance information about connection usage to Windows performance counters. Removed after version 5.0 +. | false |
| Read Buffer Size | Determines the size of the internal buffer used by Npgsql when reading. If large byte stream values are transferred from the database, the increment improves performance. | 8192 |
| Write Buffer Size | Determines the size of the internal buffer used by Npgsql when writing. The increment improves performance if large byte stream values are transferred to the database. | 8192 |
| Socket Receive Buffer Size | Determines the size of the socket receive buffer. | System-dependent |
| Socket Send Buffer Size | Determines the size of the socket send buffer. | System-dependent |
| No Reset On Close | In some cases, performance is improved by not resetting the connection state on return to the pool, but at the expense of leaking state. Use only when benchmarks show performance improvements | false |
7 Failover and load balancing parameters
| parameter | describe | The default |
|---|---|---|
| Target Session Attributes | Determine the preferred PostgreSQL target server type. | PGTARGETSESSIONATTRS, Any |
| Load Balance Hosts | Balancing across multiple hosts through load. | false |
| Host Recheck Seconds | The cache state of the control host will be treated as valid time. | 10 |
8 some small dessert parameters
| parameter | describe | The default |
|---|---|---|
| Options | Specify any validPostgreSQL connection option, surrounded by a single scale. Introduced in 5.0. | PGOPTIONS |
| Application Name | Optional application name parameter to send to the back end during connection startup. | |
| Enlist | Whether to register in the environment TransactionScope. | true |
| Search Path | Set the Schema search path. | |
| Client Encoding | Gets or sets the client_encoding parameter. | PGCLIENTENCODING |
| Encoding | Gets or sets the.NET encoding that will be used to encode/decode PostgreSQL string data. | UTF8 |
| Timezone | Gets or sets the session time zone. | PGTZ |
| EF Template Database | The database template specified when the database is created in the Entity framework. | template1 |
| EF Admin Database | The database administrator to specify when creating and deleting databases in the Entity framework. | template1 |
| Load Table Composites | Load table compound type definitions, not just individual compound types. | The wrong |
| Array Nullability Mode | Configures how an array of value types is returned when requested as an object instance. Possible values are: Never (arrays of value types are Always returned as non-nullable arrays), Always (arrays of value types are Always returned as nullable arrays), and PerInstance (the returned array type is determined at run time). | Never |
9 Compatibility Mode
| parameter | describe | The default |
|---|---|---|
| Server Compatibility Mode | Compatibility mode for a specific PostgreSQL server type. Currently supported”Redshift“, and “NoTypeLoading”, which bypasses the normal type loading mechanism from PostgreSQL directory tables and supports hard-coded lists of basic types. | none |
10 Environment Variables
In addition to linking string parameters, Npgsql also recognizes environment variables.
| The environment variable | describe |
|---|---|
| PGUSER | Behavior and link parameter User |
| PGPASSWORD | The behavior is the same as the link parameter password |
| PGPASSFILE | The behavior is the same as the link parameter passfile |
| PGSSLCERT | Behavior with link parameter sslcert |
| PGSSLKEY | Behavior and link parameter SSLkey |
| PGSSLROOTCERT | Behavior and link parameter sslRootcert |
| PGCLIENTENCODING | Behavior and link parameter client_encoding |
| PGTZ | Behavior and link parameter Default time zone. |
| PGOPTIONS | The behavior is the same as the link parameter options |
11. A summary
Introducing these parameters is boring. What’s the point? Why not link to a database and make it so complicated?
Ah, who meets a problem who knows, these parameters all save a life, another day I will talk to you about how to save me.
👓 have seen this, but also care about a thumbs-up?
👓 has been liked, but also care about a collection?
👓 are collected, but also care about a comment?