{"id":100,"date":"2009-11-10T09:45:00","date_gmt":"2009-11-10T07:45:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2009\/11\/10\/postgresql\/"},"modified":"2012-05-25T17:08:35","modified_gmt":"2012-05-25T15:08:35","slug":"postgresql","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2009\/11\/10\/postgresql\/","title":{"rendered":"POSTGRESQL"},"content":{"rendered":"
\n

psql<\/h2>\n
\n<\/div>\n
\n

tryb OS<\/h3>\n
\n\n\n\n\n<\/colgroup>\n\n\n\n\n
<\/caption>\n
psql [option] -d [dbname] -h [hostname] -U [username]<\/td>\n<\/td>\n<\/tr>\n
psql -l<\/td>\nlista baz<\/td>\n<\/tr>\n
psql -f [file]<\/td>\npobieraj komendy z pliku<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n<\/div>\n<\/div>\n

\n

tryb psql<\/h3>\n
\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/caption>\n
psql syntax:<\/th>\n<\/th>\n<\/tr>\n<\/thead>\n
\\timing<\/td>\nustaw wskazanie czas wykonania SQL<\/td>\n<\/tr>\n
\\q<\/td>\nwyjd\u017a<\/td>\n<\/tr>\n
\\password<\/td>\nustaw has\u0142o<\/td>\n<\/tr>\n
\\pset<\/td>\nzmie\u0144 ustawienia psql<\/td>\n<\/tr>\n
\\l<\/td>\npoka\u017c bazy<\/td>\n<\/tr>\n
\\d [table]<\/td>\nopis tabeli<\/td>\n<\/tr>\n
\\c [database]<\/td>\npod\u0142\u0105cz do bazy<\/td>\n<\/tr>\n
\\da<\/td>\nfunkcje agreguj\u0105ce<\/td>\n<\/tr>\n
\\db+<\/td>\nprzestrzenie tabel<\/td>\n<\/tr>\n
\\dc<\/td>\nconversions<\/td>\n<\/tr>\n
\\df+<\/td>\nfunkcje<\/td>\n<\/tr>\n
\\dg+ \\du+<\/td>\nrole<\/td>\n<\/tr>\n
\\di+<\/td>\nindeksy<\/td>\n<\/tr>\n
\\ds+<\/td>\nsekwencje<\/td>\n<\/tr>\n
\\dt+<\/td>\ntabele<\/td>\n<\/tr>\n
\\dv+<\/td>\nwidoki<\/td>\n<\/tr>\n
\\dSvtis+<\/td>\nwidoki systemowe,tabele,indeksy,sekwencje<\/td>\n<\/tr>\n
\\dn+<\/td>\nschematy<\/td>\n<\/tr>\n
\\do<\/td>\noperatory<\/td>\n<\/tr>\n
\\dp<\/td>\nuprawnienia<\/td>\n<\/tr>\n
\\encoding<\/td>\nkodowanie bazy<\/td>\n<\/tr>\n
\\l+<\/td>\nopis bazy<\/td>\n<\/tr>\n
\\z<\/td>\nobiekty z uprawnieniami<\/td>\n<\/tr>\n
\\o [file]<\/td>\nplik spool<\/td>\n<\/tr>\n
\\! [command]<\/td>\nwykonaj komend\u0119 OS<\/td>\n<\/tr>\n<\/tbody>\n
help commands:<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n
\\?<\/td>\ninfo about commands with backslash<\/td>\n<\/tr>\n
\\h<\/td>\nsql help<\/td>\n<\/tr>\n<\/tbody>\n
parametery:<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n
show all<\/td>\nparametry psql<\/td>\n<\/tr>\n
show [parametr]<\/td>\npoka\u017c parametr<\/td>\n<\/tr>\n
show search_path<\/td>\npoka\u017c parametr search_path<\/td>\n<\/tr>\n
set search_path to [other_schema]<\/td>\nustaw parametr search path na nowy schemat<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n<\/div>\n<\/div>\n<\/div>\n

\n

lokalizacje<\/h2>\n
\n\n\n\n\n<\/colgroup>\n\n\n
<\/caption>\n
default binary path<\/td>\n\/usr\/local\/psql\/pg<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n<\/div>\n<\/div>\n

\n

zmiana bie\u017c\u0105cego kontekstu:<\/h2>\n
\n
    \n
  • show search_path;\n<\/li>\n
  • \\dn;\n<\/li>\n
  • set search_path to <schema>;\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
    \n

    pg_ctl:<\/h2>\n
    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n
    <\/caption>\n
    pg_ctl status<\/td>\nstatus klastra<\/td>\n<\/tr>\n
    pg_ctl -D [cluster_dir] [action]<\/td>\nstatus klastra z okre\u015blonej lokalizacji<\/td>\n<\/tr>\n
    pg_ctl start\/stop\/restart<\/td>\nstart,stop klastra, mo\u017cna doda\u0107 -l [logfile]<\/td>\n<\/tr>\n
    pg_ctl stop -m [smart\/fast\/immediate]<\/td>\nstop klastra w okre\u015blonym -m trybie: smart(default), fast(with rolleback transactions, immediate(shutdown abort)<\/td>\n<\/tr>\n
    pg_controldata [cluser_dir]<\/td>\ninfo klastra<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    tworzenie klastra:<\/h2>\n
    \n\n\n\n\n<\/colgroup>\n\n\n
    <\/caption>\n
    initdb –pgdata\/-D [cluster_dir] [-E encoding]<\/td>\n#cluster initialization,create template1 and postgres database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    tworzenie bazy:<\/h2>\n
    \n\n\n\n<\/colgroup>\n\n\n
    <\/caption>\n
    createdb [dbname] [-D tablespace] [-E encoding] [-O owner] [-T template to create new database]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    polecenia SELECT dla administratora:<\/h2>\n
    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n
    <\/caption>\n
    SELECT pg_database_size('[dbname]’);<\/td>\nrozmiar bazy<\/td>\n<\/tr>\n
    SELECT pg_size_pretty(pg_database_size('[dbname]’));<\/td>\nrozmiar bazy w formacie<\/td>\n<\/tr>\n
    SELECT pg_size_pretty(pg_total_relation_size('[table]’));<\/td>\nrozmiar tabel z indeksami<\/td>\n<\/tr>\n
    SELECT pg_size_pretty(pg_relation_size('[table]’));<\/td>\nrozmiar bazy bez indeks\u00f3w<\/td>\n<\/tr>\n
    SELECT spcname FROM pg_tablespace;<\/td>\n<\/td>\n<\/tr>\n
    SELECT relname,relpages FROM pg_class WHERE;<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    pg_dump(eksport)<\/h2>\n
    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
    <\/caption>\n
    pg_dump [db_name] > [file]<\/td>\ndump in plaintext format<\/td>\n<\/tr>\n
    pg_dump -t '[table]’ [db_name] > [file]<\/td>\n<\/td>\n<\/tr>\n
    pg_dump -Fc<\/td>\ndump in pg_restore format<\/td>\n<\/tr>\n
    pg_dumpall > [file]<\/td>\ndump all databases (dump the cluster)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    import:<\/h2>\n
    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
    <\/caption>\n
    psql [db] < [plik]<\/td>\nimport z pliku w formacie tekstowym<\/td>\n<\/tr>\n
    psql -f [file] postgres<\/td>\nimport z pliku w formacie tekstowym (de facto wykonanie polece\u0144 z pliku<\/td>\n<\/tr>\n
    pg_restore -d [baza] [plik]<\/td>\nimport z pliku w formacie pg_restore<\/td>\n<\/tr>\n
    pg_restore -l [plik]<\/td>\npoka\u017c zawarto\u015b\u0107 dumpfile<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    \n<\/div>\n<\/div>\n

    \n

    zmiana IP dla hosta:<\/h2>\n
    \n
      \n
    1. change in $PG_DATA\/postgresql.conf\n<\/li>\n
    2. change in $PG_DATA\/pg_hba.conf (according manual – chapter 20: Client Authentication)\n<\/li>\n<\/ol>\n<\/div>\n<\/div>\n
      \n

      zarz\u0105dzanie uprawnieniami i rolami:<\/h2>\n
      \n
        \n
      • sprwdzenie:\n
          \n
        • \\dg #sprawdzenie uprawnie\u0144 systemowych\n<\/li>\n
        • SELECT * FROM pg_roles;\n<\/li>\n<\/ul>\n<\/li>\n
        • tworzenie roli:\n
            \n
          • CREATE ROLE [role_name];\n<\/li>\n<\/ul>\n<\/li>\n
          • zmiana:\n
              \n
            • ALTER ROLE [role_name] SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT [connlimit] | PASSWORD [password] | ENCRYPTED | UNENCRYPTED | VALID UNTIL [timestamp]\n<\/li>\n<\/ul>\n<\/li>\n
            • zmiana has\u0142a:\n
                \n
              • ALTER ROLE [role] PASSWORD '[pass]’;\n<\/li>\n<\/ul>\n<\/li>\n
              • nadanie uprawnienia do roli\n
                  \n
                • GRANT [privs] ON [object] TO [role];\n<\/li>\n<\/ul>\n<\/li>\n
                • zmiana uprawnie\u0144 do obiektu:\n
                    \n
                  • \\z #sprawd\u017a uprawnienia\n<\/li>\n
                  • =xxxx #uprawnienia nadane na PUBLIC\n<\/li>\n
                  • username=xxxx #uprawnienia nadane dla usera\n<\/li>\n
                  • groupname=xxxx #privileges nadane dla grupy\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n
                    \n

                    flagi uprawnie\u0144<\/h3>\n
                    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
                    <\/caption>\n
                    r<\/td>\nSELECT („read”)<\/td>\n<\/tr>\n
                    w<\/td>\nUPDATE („write”)<\/td>\n<\/tr>\n
                    a<\/td>\nINSERT („append”)<\/td>\n<\/tr>\n
                    d<\/td>\nDELETE<\/td>\n<\/tr>\n
                    R<\/td>\nRULE<\/td>\n<\/tr>\n
                    x<\/td>\nREFERENCES<\/td>\n<\/tr>\n
                    t<\/td>\nTRIGGER<\/td>\n<\/tr>\n
                    X<\/td>\nEXECUTE<\/td>\n<\/tr>\n
                    U<\/td>\nUSAGE<\/td>\n<\/tr>\n
                    C<\/td>\nCREATE<\/td>\n<\/tr>\n
                    T<\/td>\nTEMPORARY<\/td>\n<\/tr>\n
                    arwdRxt<\/td>\n– ALL PRIVILEGES (for tables)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                    \n<\/div>\n<\/div>\n<\/div>\n

                    \n

                    misc:<\/h2>\n
                    \n
                      \n
                    • ALTER TABLE [table] ALTER COLUMN [column] TYPE int USING [column]::int; #change column type from char to in\n<\/li>\n
                    • variable PGDATA point to cluster catalog\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                      psql tryb OS psql [option] -d [dbname] -h [hostname] -U [username] psql -l lista baz psql -f [file] pobieraj komendy z pliku tryb psql psql syntax: \\timing ustaw wskazanie czas wykonania SQL \\q wyjd\u017a \\password ustaw has\u0142o \\pset zmie\u0144 ustawienia psql \\l poka\u017c bazy \\d [table] opis tabeli \\c [database] pod\u0142\u0105cz do bazy \\da funkcje […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[17],"tags":[93],"class_list":["post-100","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=100"}],"version-history":[{"count":3,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/100\/revisions"}],"predecessor-version":[{"id":417,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/100\/revisions\/417"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}