sudo service postgresql start
sudo service postgresql status
sudo service postgresql stop
# As the Linux postgres user run the psql command
sudo -u postgres psql
# Run the psql as the user postgres and prompt for the user's password
psql -U postgres -W
# or login as postgres user on system, then call psql
sudo -u postgres -i
Basically there must be an OS username that matches a user in the DB (when creating a new db system the user is commonly 'postgres' and has superuser privileges.)
https://www.postgresql.org/docs/current/user-manag.html
https://www.postgresql.org/docs/current/client-authentication.html
SELECT version();
SHOW server_version();
SHOW server_version_num;
pg_config --version
psql --version
psql -V
# as postgres user from Linux
psql -c "SELECT version();"
NB: psql (client) ersion may not be the same version as the database engine (server version).
\l
\q
https://www.postgresql.org/docs/12/ddl-priv.html
https://www.postgresql.org/docs/current/sql-grant.html
\z
-- existing privileges for tables and columns
\dp
To read the format, see under Notes, https://www.postgresql.org/docs/9.3/sql-grant.html
-- privileges granted to rolename
rolename=xxxx
-- privileges granted to public
=xxxx
-- /yyyy is role that granted this privilege
rolename=xxxx/yyyy
C create
T temporary
c connect
https://stackoverflow.com/questions/25691037/postgresql-permissions-explained
Granting access to users
-- grant connect to the database
GRANT CONNECT ON DATABASE db_name to user;
-- grant usage on schema
GRANT USAGE ON SCHEMA schema_name to user;
-- DML statements
grant select, insert, update, delete on all tables in schema schema_name to user;
-- all priv
grant all privileges on all tables in schema schema_name to username;
grant all privileges on all sequences in schema schema_name to username;
grant all privileges on database db_name to username;
-- permission to create db
alter user uisername createdb;
create db then revoke
create database db_name with owner some_user;
-- then check db and privileges
\l
-- will show nothing under the access privileges line
revoke connect on database db_name from public;
-- then check db and privileges again
\l
-- =T/some_user
-- some_user=CTc/someuser
-- line 1: means public has temporary 'T' ? grant done by some_user
-- line 2: some_user has privileges C (create) T (temporary) and c (connect), granted by some_user
\c databasename
List schema, table names, type and owners
\dt
psql equivalent is:
\d table_name
-- or
\d+ table_name
ALTER TABLE table_name
RENAME old_col TO new_col;
Drop tables, data types
Cannot reorder enum once created Can add values
\dT
\dT+
Get schema https://stackoverflow.com/questions/14486241/how-can-i-export-the-schema-of-a-database-in-postgresql
https://www.postgresql.org/docs/11/app-pgdump.html
pg_dump: server version: 11.10; pg_dump version: 9.2.24 pg_dump: aborting because of server version mismatch
$ pg_dump --version pg_dump (PostgreSQL) 9.2.24
yum info postgresql-devel
Installed Packages Name : postgresql-devel Arch : x86_64 Version : 9.2.24 Release : 6.el7_9 Size : 4.0 M Repo : installed From repo : rhel-7-server-rhui-rpms Summary : PostgreSQL development header files and libraries URL : http://www.postgresql.org/ License : PostgreSQL Description : The postgresql-devel package contains the header files and libraries : needed to compile C or C++ applications which will directly interact : with a PostgreSQL database management server. It also contains the ecpg : Embedded C Postgres preprocessor. You need to install this package if you want : to develop applications which will interact with a PostgreSQL server.
Available Packages Name : postgresql-devel Arch : x86_64 Version : 9.2.24 Release : 7.el7_9 Size : 952 k Repo : rhel-7-server-rhui-rpms/7Server/x86_64 Summary : PostgreSQL development header files and libraries URL : http://www.postgresql.org/ License : PostgreSQL Description : The postgresql-devel package contains the header files and libraries : needed to compile C or C++ applications which will directly interact : with a PostgreSQL database management server. It also contains the ecpg : Embedded C Postgres preprocessor. You need to install this package if you want : to develop applications which will interact with a PostgreSQL server.
yum list