Database Using psql in PostgreSQL
With psql, developers, database managers, and users can connect to PostgreSQL. PostgreSQL includes psql’s advanced command-line interface. Questions, administrative tasks, scripting, data import/export, and reporting are possible. PHP connection creation parameters, authentication methods, and standard procedures are explained in this article.
The interactive terminal software psql lets users query PostgreSQL servers and view the results. From its beginnings, PostgreSQL has been essential for terminal users without a GUI. PHP supports shortcuts, history files, session behaviour, configurability, and text editor integration.
Basic Connection Syntax
Typing the database name at the command prompt connects PostgreSQL to psql the easiest:
psql <database_name>
Example: Connecting to testdb:
psql testdb
PSQL uses the operating system user’s name if no database name is given. Since many tools presume this default, this is a good alternative. PostgreSQL will prompt if the connection is successful. The database superuser will see # or the database name followed by => (e.g., mydb=>).
Connection Parameters
Psql has connection-related environment variables and command-line arguments. Connect to servers on multiple hosts, ports, or with unique credentials with these options.
Host (-h or PGHOST): -h or PGHOST specifies the database server’s hostname or IP address. If left empty, Psql connects to a local Unix-domain socket. Windows connections default to localhost.
Port (-p or PGPORT): The TCP/IP port number that the PostgreSQL server is listening for connections on is specified by the port argument, or its associated environment variable PGPORT. This is important because in order for a client application, such as psql, to connect to the PostgreSQL server, it must know the correct port.
User (-U or PGUSER): The -U (or PGUSER) argument specifies the PostgreSQL user name when connecting to a database using the psql command-line client. This argument is important since PostgreSQL user accounts are logically distinct from operating system user accounts, even though it’s handy to correspond. The current operating system user’s name is used as PostgreSQL’s user name if the -U option is not used. One might use psql -U app appdb to connect to a database as app.
Password (-W or PGPASSWORD, .pgpass): A password is required for psql with the -W option. The PGPASSWORD environment variable is not recommended for security because ps can view passwords. .pgpass files are safer. �/.pgpass or %APPDATA%\postgresql\pgpass.conf on Windows encrypts libpq passwords on Unix-like platforms. Security is ensured by only the owner reading or writing the.pgpass file.
Database name (-d or PGDATABASE): Connecting to a database requires specifying its name (-d or PGDATABASE). Many utilise this when the login and database names differ.
No Password (-w): The –no-password option prevents psql from asking for a password. It’s beneficial when scripts use.pgpass to authenticate without human intervention.
Code Example:
CREATE TABLE staff (
id SERIAL PRIMARY KEY,
fullname VARCHAR(50),
salary NUMERIC
);
INSERT INTO staff (fullname, salary) VALUES
('Harshitha', 70000),
('Tharun', 65000),
('Thendral', 72000);
SELECT * FROM staff;
SELECT current_user, current_database();
DROP TABLE staff;Output:
CREATE TABLE
INSERT 0 3
id | fullname | salary
----+-----------+--------
1 | Harshitha | 70000
2 | Tharun | 65000
3 | Thendral | 72000
(3 rows)
current_user | current_database
--------------------------+------------------------
user_43vjjau6m_43vwcu2ed | sandbox_db
(1 row)
DROP TABLEAuthentication Methods
Within PostgreSQL’s client/server architecture, Pg_hba.conf in the data directory handles client authentication. Many authentication methods are used to determine which IP addresses can access certain databases in this file. Before using the first matching rule, the file is read from top to bottom. Changes to pg_hba.conf require server restart or reload.
Normal authentication methods include:
Trust: Through trust, unconditional connection is possible. Use this strategy only when operating-system-level security is sufficient.
MD5/Password: A cleartext or MD5 password is required. Many use MD5. Database passwords in PostgreSQL are managed via the \password psql command or the CREATE ROLE or ALTER ROLE SQL commands, and are distinct from operating system user passwords.
Peer/Ident: Peer/Ident checks users’ operating system user names on Unix-like platforms. Ident searches the client’s host’s ident server, while peer authentication uses Unix-domain sockets for local connections.
GSSAPI/Kerberos: Single sign-on and secure authentication are possible with GSSAPI/Kerberos. Krbsrvname specifies the Kerberos service name.
SSPI: The Windows-specific Security Support Provider Interface (SSPI) architecture allows safe authentication and Windows Single Sign-On (SSO). SSPI is a PostgreSQL client authentication technique. PostgreSQL uses SSPI in “negotiate mode,” which means it first tries Kerberos and then NTLM if Kerberos is unavailable.
LDAP: PostgreSQL clients, including psql users, can authenticate against an LDAP directory server for password verification. This method validates user name and password pairs, therefore the user must be a PostgreSQL role before LDAP can be used for authentication.
PAM: PAM (Pluggable Authentication Modules) authentication in PostgreSQL uses the operating system’s PAM service to verify user credentials. When psql connects to a PostgreSQL database using PAM authentication, the server validates the user name and password.
Certificate Authentication: PostgreSQL certificate authentication is only available for SSL connections and uses SSL client certificates to confirm a user’s identity when connecting to a database. This solution removes the need for a password prompt by requiring the client to provide a valid and trusted certificate to the PostgreSQL server.
Interactive vs. Non-interactive
Psql operates two ways:
Interactive Mode: The main method by which users communicate directly with a PostgreSQL database via a command-line interface is known as “Interactive Mode” in PHP. At the command prompt of your operating system, you usually type psql followed by the database name to enter this mode, for example, psql testdb. Your current operating system user name will be used as the database name by default if no database name is supplied by psql.
The command prompt changes after a successful connection, typically showing the database name with => (e.g., mydb=>). It may be mydb=# if you are a database superuser. You can directly input SQL commands in this interactive environment, such as SELECT version();, but they must end with a semicolon (;) or the \g meta-command in order to be executed. Psql thinks that the statement continues if a semicolon is left off, and the prompt may change to -> to denote multiline input.
Non-interactive Mode (Scripting): Psql’s non-interactive mode (scripting) is useful for shell scripts and scheduled operations because it automates tasks and executes commands without user participation. In this mode, psql processes commands from the operating system prompt or a file instead of standard input. Executing a single SQL command or psql internal meta-command requires the -c (or –cmd) option and the command string, usually in double quotes.
A query like psql -c “SELECT version();” will execute and quit. Multiple SQL statements given with -c must be combined with a semicolon. Though piping can execute a mix of SQL commands and backslash commands, the -c option can only execute a single query string. To execute a succession of commands, especially for automated tasks, use -f (or –file). Using this option, psql reads and executes all SQL statements and meta-commands in a filename and terminates.
This method is ideal for deploying application code updates, performing logical backups, generating reports, and scheduling jobs with pgAgent or cron. A file with several script statements must be separated by semicolons. The -f option lets psql restore plain-text SQL backups non-interactively.
Common Operations and Customization
Executing Queries: After connecting, type SQL queries like SELECT version(); into the psql prompt.
Importing/Exporting Data: Utilise the \copy meta-command to efficiently transfer data across files and tables. For instance, \copy my_table FROM ‘data.csv’ CSV HEADER uses CSV. To export, copy my_table to ‘data.csv’ WITH CSV HEADER. Pg_dump may logically backup databases as SQL script files.
Customization: Put user preferences and startup instructions in a.psqlrc file in your home directory to personalise PHP. Shortcuts, environment variables, and prompt customisation are possible.
Troubleshooting Connection Issues
Multiple causes cause connection difficulties, which are common. The server logs (log_connections, log_disconnections) and error messages expose Psql.
“Connection refused” / “No such file or directory”: PostgreSQL may be disabled or connecting to the wrong host or port if it says “Connection refused” or “No such file or directory”. Start and listen on the specified port and address a server.
“no pg_hba.conf entry”: The server denied access because It had no rule. Check host, database, user, and method pg_hba.conf.
“password authentication failed”: For example, “password authentication failed” means the password is incorrect or that Kerberos or Ident failed. Examine the password or authentication settings.
“user does not exist”: The “user does not exist” error message means the PostgreSQL database server could not find a user account matching your connection request. Because PostgreSQL and OS user accounts are separate, this often happens. Unless otherwise specified, psql uses the current operating system user as the PostgreSQL user.
“database does not exist”: Your database does not exist if “database does not exist” appears. Run createdb or CREATE DATABASE.
“permission denied to create database”: “Permission denied to create database” means your user account should not build a database. Database creation is restricted to superusers.
Psql is needed to administer PostgreSQL databases due to their meta-commands and command-line parameters. Management and use of PostgreSQL require knowledge of its connection protocols, authentication guidelines, and troubleshooting methods.