Last active
May 10, 2021 08:43
-
-
Save bushidocodes/4a02f6cc865d280b10400319b128cd92 to your computer and use it in GitHub Desktop.
Revisions
-
bushidocodes revised this gist
Nov 10, 2016 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -31,7 +31,7 @@ postgres=# \q One of the big changes between 9.3 and 9.6 is the upgrade of pgAdmin III to pgAdmin IV. The interface seems to have changed, and I couldn't find a way to change my localhost permissions from md5 to trust in the GUI. As I result, I just had to manually edit the configuration file as shown in the [Ubuntu instructions](https://learn.fullstackacademy.com/workshop/5717c9edf8d81d03002472ba/content/5717ca218c4fe5030064e166/text) Accepting the defaults during the Windows installer, my pg_hba.conf file was located in C:\Program Files\PostgreSQL\9.6\data. -
Nov 10, 2016 . 1 changed file with 5 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -108,4 +108,8 @@ spmcbride1201=# \q ➜ ~ ``` Going back to pgAdmin4, I was able to see the new table after hitting refresh. I was then successfully able to create the `~/.psqlrc` file to get the custom prompt. I then completed the rest of the Postgres prework. -
Nov 10, 2016 . 1 changed file with 19 additions and 102 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -56,139 +56,56 @@ I wasn't clear about the command `sudo upstart restart postgresql` but I exporte `export PATH=$PATH:/mnt/c/Program\ Files/PostgreSQL/9.6/bin` to use `pg_ctl.exe` to restart the file. ``` pg_ctl.exe restart -D C:\\Program\ Files\\PostgreSQL\\9.6\\data\ waiting for server to shut down.... done server stopped server starting 2016-11-09 22:20:55 EST LOG: redirecting log output to logging collector process 2016-11-09 22:20:55 EST HINT: Future log output will appear in directory "pg_log". ➜ ~ ``` I moved on to create my user to see if it would work without a restart, and I got some errors. ``` ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ``` My workaround for this was to create my userid in pgAdmin. I knew that this was successful because the createuser command now failed on my username already existing ``` ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: creation of new role failed: ERROR: role "spmcbride1201" already exists ``` I was then able to create a db, enter the psql shell, and create a table. ``` ➜ ~ createdb spmcbride1201 ➜ ~ psql psql (9.5.5, server 9.6.1) WARNING: psql major version 9.5, server major version 9.6. Some psql features might not work. Type "help" for help. spmcbride1201=# help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit spmcbride1201=# CREATE TABLE films ( code char(5) CONSTRAINT firstkey PRIMARY KEY, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(10), len interval hour to minute ); CREATE TABLE spmcbride1201=# \q ➜ ~ ``` Going back to pgAdmin4, I was able to see the new table after hitting refresh. -
Nov 10, 2016 . 1 changed file with 43 additions and 154 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,5 +1,22 @@ When I went through Eliot's excellent tutorial, I decided to try to push forward with the latest-and-greatest version of Postgres to troubleshoot the directions with the newer version (the 64-bit Version 9.6.1.1 x64 Windows installer). But first I had cleanup to do. I had mistakenly assumed that I was running postgresql under WSL and installed some packages. I removed them to make sure I didn't end up with collisions over Linux and Windows binaries sharing the same name in $PATH. ``` ➜ ~ sudo apt-get remove postgresql Reading package lists... Done Building dependency tree Reading state information... Done The following packages will be REMOVED: postgresql 0 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. After this operation, 59.4 kB disk space will be freed. Do you want to continue? [Y/n] y (Reading database ... 38341 files and directories currently installed.) Removing postgresql (9.5+173) ... ➜ ~ ``` When I ran the `psql -p 5432 -h localhost -U postgres` for the first time in BASH, I was actually able to get into the prompt using the postgres user I created during the Windows Installation wizard. ```bash @@ -21,170 +38,42 @@ Accepting the defaults during the Windows installer, my pg_hba.conf file was loc This can be edited in VIM like such: `vim /mnt/c/Program\ Files/PostgreSQL/9.6/data/pg_hba.conf` and can be edited to look like the following: ``` # TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust ``` I wasn't clear about the command `sudo upstart restart postgresql` but I exported the Windows binary folder to my BASH $PATH `export PATH=$PATH:/mnt/c/Program\ Files/PostgreSQL/9.6/bin` to try to use `pg_ctl.exe` to restart the file ``` .\pg_ctl.exe restart pg_ctl: no database directory specified and environment variable PGDATA unset ``` And this failed. I decided to move on to create my user to see if it would work without a restart, and I got some errors. ``` ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ➜ ~ createuser --help createuser creates a new PostgreSQL role. ``` ➜ ~ ➜ ~ ➜ ~ -
Nov 10, 2016 . 1 changed file with 0 additions and 0 deletions.There are no files selected for viewing
File renamed without changes. -
Nov 10, 2016 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,305 @@ When I went through Eliot's excellent tutorial, I decided to try to push forward with the latest-and-greatest version of Postgres to troubleshoot the directions with the newer version (the 64-bit Version 9.6.1.1 x64 Windows installer). When I ran the `psql -p 5432 -h localhost -U postgres` for the first time in BASH, I was actually able to get into the prompt using the postgres user I created during the Windows Installation wizard. ```bash ➜ ~ psql -p 5432 -h localhost -U postgres psql (9.5.5, server 9.6.1) WARNING: psql major version 9.5, server major version 9.6. Some psql features might not work. Type "help" for help. postgres=# \q ``` One of the big changes between 9.3 and 9.6 is the upgrade of pgAdmin III to pgAdmin IV. The interface seems to have changed, and I couldn't find a way to change my localhost permissions from md5 to trust in the GUI. As I result, I just had to manually edit the configuration file as shown in the (Ubuntu instructions)[https://learn.fullstackacademy.com/workshop/5717c9edf8d81d03002472ba/content/5717ca218c4fe5030064e166/text] Accepting the defaults during the Windows installer, my pg_hba.conf file was located in C:\Program Files\PostgreSQL\9.6\data. This can be edited in VIM like such: `vim /mnt/c/Program\ Files/PostgreSQL/9.6/data/pg_hba.conf` ``` ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ➜ ~ createuser --help createuser creates a new PostgreSQL role. Usage: createuser [OPTION]... [ROLENAME] Options: -c, --connection-limit=N connection limit for role (default: no limit) -d, --createdb role can create new databases -D, --no-createdb role cannot create databases (default) -e, --echo show the commands being sent to the server -E, --encrypted encrypt stored password -g, --role=ROLE new role will be a member of this role -i, --inherit role inherits privileges of roles it is a member of (default) -I, --no-inherit role does not inherit privileges -l, --login role can login (default) -L, --no-login role cannot login -N, --unencrypted do not encrypt stored password -P, --pwprompt assign a password to new role -r, --createrole role can create new roles -R, --no-createrole role cannot create roles (default) -s, --superuser role will be superuser -S, --no-superuser role will not be superuser (default) -V, --version output version information, then exit --interactive prompt for missing role name and attributes rather than using defaults --replication role can initiate replication --no-replication role cannot initiate replication -?, --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 (not the one to create) -w, --no-password never prompt for password -W, --password force password prompt Report bugs to <pgsql-bugs@postgresql.org>. ``` ➜ ~ sudo apg-get remove postgres [sudo] password for spmcbride1201: sudo: apg-get: command not found ➜ ~ sudo apt-get remove postgres Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgres ➜ ~ sudo apg-get uninstall postgres sudo: apg-get: command not found ➜ ~ sudo apg-get uninstallD postgres sudo: apg-get: command not found ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ postgres zsh: command not found: postgres ➜ ~ apt-get --help apt 1.2.12 (amd64) Usage: apt-get [options] command apt-get [options] install|remove pkg1 [pkg2 ...] apt-get [options] source pkg1 [pkg2 ...] apt-get is a command line interface for retrieval of packages and information about them from authenticated sources and for installation, upgrade and removal of packages together with their dependencies. Most used commands: update - Retrieve new lists of packages upgrade - Perform an upgrade install - Install new packages (pkg is libc6 not libc6.deb) remove - Remove packages purge - Remove packages and config files autoremove - Remove automatically all unused packages dist-upgrade - Distribution upgrade, see apt-get(8) dselect-upgrade - Follow dselect selections build-dep - Configure build-dependencies for source packages clean - Erase downloaded archive files autoclean - Erase old downloaded archive files check - Verify that there are no broken dependencies source - Download source archives download - Download the binary package into the current directory changelog - Download and display the changelog for the given package See apt-get(8) for more information about the available commands. Configuration options and syntax is detailed in apt.conf(5). Information about how to configure sources can be found in sources.list(5). Package and version choices can be expressed via apt_preferences(5). Security details are available in apt-secure(8). This APT has Super Cow Powers. ➜ ~ find postgres find: ‘postgres’: No such file or directory ➜ ~ whois zsh: command not found: whois ➜ ~ which postgres postgres not found ➜ ~ sudo apg-get uninstall postgres-9.4 sudo: apg-get: command not found ➜ ~ sudo apt-get uninstall postgres-9.4 E: Invalid operation uninstall ➜ ~ sudo apt-get remove postgres-9.4 Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgres-9.4 E: Couldn't find any package by glob 'postgres-9.4' E: Couldn't find any package by regex 'postgres-9.4' ➜ ~ sudo apt-get remove postgres-9.% Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgres-9.% E: Couldn't find any package by glob 'postgres-9.%' E: Couldn't find any package by regex 'postgres-9.%' ➜ ~ sudo apt-get remove postgres% Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgres% ➜ ~ sudo apt-get remove postgres* zsh: no matches found: postgres* ➜ ~ sudo apt-get remove postgres Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgres ➜ ~ sudo apt-get remove postgresql Reading package lists... Done Building dependency tree Reading state information... Done The following packages will be REMOVED: postgresql 0 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. After this operation, 59.4 kB disk space will be freed. Do you want to continue? [Y/n] y (Reading database ... 38341 files and directories currently installed.) Removing postgresql (9.5+173) ... ➜ ~ ➜ ~ ➜ ~ ➜ ~ ➜ ~ psql -p 5432 -h localhost -U postgres psql (9.5.5, server 9.6.1) WARNING: psql major version 9.5, server major version 9.6. Some psql features might not work. Type "help" for help. postgres=# \quit ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ➜ ~ createuser --interactive spmcbride Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: FATAL: role "spmcbride1201" does not exist ➜ ~ sudo -u postgres createuser owning_user sudo: setresuid() [1000, 112, 1000] -> [-1, 0, -1]: Operation not permitted sudo: unable to set runas group vector: Operation not permitted sudo: PERM_ROOT: setresuid(0, -1, 0): Operation not permitted sudo: unable to open /var/run/sudo/ts/spmcbride1201: Permission denied [sudo] password for spmcbride1201: ^C sudo: PERM_ROOT: setresuid(0, -1, 0): Operation not permitted sudo: 1 incorrect password attempt sudo: PERM_ROOT: setresuid(0, -1, 0): Operation not permitted ➜ ~ ➜ ~ sudo -u postgres createuser spmcbride1201 sudo: setresuid() [1000, 112, 1000] -> [-1, 0, -1]: Operation not permitted sudo: unable to set runas group vector: Operation not permitted sudo: PERM_ROOT: setresuid(0, -1, 0): Operation not permitted sudo: unable to open /var/run/sudo/ts/spmcbride1201: Permission denied [sudo] password for spmcbride1201: sudo: PERM_ROOT: setresuid(0, -1, 0): Operation not permitted spmcbride1201 is not in the sudoers file. This incident will be reported. ➜ ~ ➜ ~ ➜ ~ ➜ ~ createuser --interactive spmcbride1201 Shall the new role be a superuser? (y/n) y createuser: creation of new role failed: ERROR: role "spmcbride1201" already exists ➜ ~ createdb spmcbride1201 ➜ ~ psql psql (9.5.5, server 9.6.1) WARNING: psql major version 9.5, server major version 9.6. Some psql features might not work. Type "help" for help. spmcbride1201=# psql spmcbride1201-# ls spmcbride1201-# help spmcbride1201-# \quit ➜ ~ ➜ ~ ➜ ~ psql psql (9.5.5, server 9.6.1) WARNING: psql major version 9.5, server major version 9.6. Some psql features might not work. Type "help" for help. spmcbride1201=# spmcbride1201=# spmcbride1201=# help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit spmcbride1201=# \? spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# \? spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# spmcbride1201=# CREATE TABLE films ( code char(5) CONSTRAINT firstkey PRIMARY KEY, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(10), len interval hour to minute ); CREATE TABLE spmcbride1201=# \q ➜ ~ export PATH=$PATH:/mnt/c/Program\ Files/PostgreSQL/9.6/bin