Tag: postgres

  • 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/