Tag: PostgreSQL

  • PostgreSQL connect to database using psql

    PostgreSQL connect to database using psql

    To connect to the PostgreSQL database server using psql command line, run

    su - postgres
psql

    If you have a PostgreSQL user and password, you can use

    psql -h SERVER_IP_HERE -p 5432 -U USER_NAME_HERE -W

    Example

    psql -h students.arrhwfpckmeo.ap-southeast-2.rds.amazonaws.com -p 5432 -U aite_students -W

    -W option is to force asking password.

    See PostgreSQL

  • postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files

    When i restart postgresql, i get following error

    postgres@vmi465483:~$ systemctl restart postgresql.service
Failed to restart postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
See system logs and 'systemctl status postgresql.service' for details.
postgres@vmi465483:~$

    This is because you are running the command as user postres.

    Login as user “root”, you will be able to restart postgres with command

    systemctl restart postgresql

    See PostgreSQL

  • Run PostgreSQL in Docker

    To run PostgreSQL on docker, create a directory for saving the data presistant

    mkdir -p /opt/my-postgresql

    run docker container

    docker run --name my-postgresql \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=serverok123 \
    -e POSTGRES_USER=serverok \
    -e POSTGRES_DB=serverok \
    -v /opt/my-postgresql:/var/lib/postgresql/data \
    -d postgres

    In above, change the value for POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD as needed.

    Connect to PostgreSQL server

    To connect to PostgreSQL server, run

    docker container exec -ti my-postgresql bash

    Now you are inside PostgreSQL conainer, to login, run

    psql -U serverok -W

    It will ask for password. Once you enter password, you will be in PostgreSQL command line.

    See docker

  • PostgreSQL Export table into CSV file

    PostgreSQL Export table into CSV file

    To export a table into CSV file, run

    \copy TABLE_NAME to 'FILE_NAME.csv' csv header;

    This will save the CSV file in current directory.

  • PostgreSQL List all databases

    PostgreSQL List all databases

    To list all databases in the PostgreSQL database, first connect to the PostgreSQL database using psql command

    su - postgres
psql

    Now run

    \list

    This will list all available databases.

    To change to a database, use the command /connect

    \connect DBNAME

    Once you are connected to a database, you can list all tables in a database with the command

    \dt

  • PostgreSQL Create Database

    To create a database, run

    CREATE DATABASE "DB_NAME_HERE";

    Creating user, grant permissions

    CREATE USER USER_NAME_HERE WITH PASSWORD 'BAPT788HZutBdV';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;
GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

    Creating a User and Database using psql

    su - postgres
createuser USERNAME_HERE
createdb --encoding=UTF8 --owner=USERNAME_HERE DB_NAME_HERE
  • PostgreSQL Delete a Database

    PostgreSQL Delete a Database

    To delete a PostgreSQL database, run

    DROP DATABASE DB_NAME;

    Example

    DROP DATABASE "odoodb2";

    PostgreSQL drop database

  • Backup PostgreSQL Database

    To backup PostgreSQL database, run

    pg_dump  -h HOSTNMAME -p PORT -U USER -d DBNAME -W -f file.psql

    Example

    pg_dump  -h ukgaa1vy7o672pj.caqdhl3x5dow.us-east-1.rds.amazonaws.com -p 5432 -U serveroku -d serverokdb -W -f file.psql

    If you don’t have PostgreSQL installed, you can use docker, pull the appropriate version of docker image, in this case i used version 13.5, same version used by Amazon RDS, then run

    docker run --rm -ti -e PGPASSWORD='PW_HERE' postgres:13.5 pg_dump -h serverok.ckdva7obgq6c.us-west-1.rds.amazonaws.com -U USER_HERE -d DB_NAME_HERE > backup.psql

    It will prompt for the password, once you enter the password, the database will be backed up to file backup.psql

    PostgreSQL

  • Create user in PostgreSQL

    Method 1

    To create user, become user “postgres” with

    su - postgres

    now run

    createuser USER_NAME

    Method 2

    You can run following commands in psql prompt

    CREATE USER USER_NAME_HERE WITH PASSWORD 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;
GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

    https://www.postgresql.org/docs/8.0/static/sql-createuser.html

    PostgreSQL

  • pg_ctl

    pg_ctl is used to start and stop the PostgreSQL server.

    To start PostgreSQL, run

    pg_ctl start -D /path/to/datadir

    You can also use stop/restart.

    pg_ctl stop -D /path/to/datadir
pg_ctl restart -D /path/to/datadir

    Back to PostgreSQL

  • PostgreSQL

    psql commands

    \lList databases
    \l+List databases
    \c DB_NAMEconnect to a database
    \dulist users
    \dtlist tables
    \d TABLE_NAMEdescribe a table
    \dn+List of schemas

    To install PostgreSQL on Ubuntu/Debian, run

    apt-get install -y postgresql

    Start it with

    service postgresql start

    By default user “postgres” have full access on PostgreSQL. To create a user, run

    su postgres
createuser -P USERNAME

    To create a database, run

    createdb -T template0 -E UTF8 -O USERNAME DBNAME

    You can see help with

    postgres@ok-vm:~$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.
postgres@ok-vm:~$

    run psql 

    sudo -u postgres psql

    Run a command in psql

    sudo -u postgres psql -c "\du"

    List all tables in a database

    sudo -u postgres psql -c "\c serverok_db" -c "\dt"

    List postgresql extensions

    sudo -u postgres psql -c "SELECT * FROM pg_available_extensions;"

    Extensions are stored in folder /usr/share/postgresql/16/extension/