Difference between revisions of "Postgresql"
| (9 intermediate revisions by the same user not shown) | |||
| Line 18: | Line 18: | ||
FYI, haven't tried but look like a nicer app to control Postgres is https://postgresapp.com. | FYI, haven't tried but look like a nicer app to control Postgres is https://postgresapp.com. | ||
| + | |||
| + | If you are not using the system registered database you can all simply use the following commands. | ||
| + | |||
| + | pg_ctl -D ./data start | ||
| + | pg_ctl -D ./data stop | ||
| + | |||
| + | == Initialize a database directory == | ||
| + | To initialize a database directory use the following command. | ||
| + | |||
| + | initdb -D <data_directory> | ||
| + | |||
| + | == Environment Path == | ||
| + | To add PostgreSQL in to your environment path. | ||
| + | |||
| + | export POSTGRESQL_HOME=/Library/PostgreSQL/11 | ||
| + | PATH=$PATH:$POSTGRESQL_HOME/bin | ||
| + | |||
| + | |||
| + | == Debian == | ||
| + | You can start Postgres in Debian using | ||
| + | /etc/init.d/postgresql start | ||
| + | |||
| + | Create a database and user | ||
| + | sudo su - postgres | ||
| + | createdb testdb | ||
| + | createuser testdb | ||
| + | |||
| + | To create a password for the user either use | ||
| + | createuser testdb --pwprompt | ||
| + | or | ||
| + | psql | ||
| + | ALTER USER 'testdb' WITH PASSWORD 'testdb'; | ||
| + | |||
| + | To have the new user be a superuser add the following. | ||
| + | createuser --superuser testdb --pwprompt | ||
| + | |||
| + | == Run file and commands from CLI == | ||
| + | PSQL will ask for your password every time unless you set it as an environment variable. | ||
| + | export PGPASSWORD=Password123 | ||
| + | |||
| + | To run a command. | ||
| + | psql -U user_name -d database_name -c "SELECT * FROM tblExample" | ||
| + | |||
| + | To run a file. | ||
| + | psql -U user_name -d database_name -f ./file.sql | ||
| + | |||
| + | == Issues == | ||
| + | |||
| + | Error Connecting to database FATAL : no pg_hba.conf entry for host | ||
| + | I had to add the host ip address into the pg_hba.conf file. | ||
| + | |||
| + | location of file: ./data/pg_hba.conf | ||
| + | |||
| + | Added the following line and then restarted Postgres. | ||
| + | host all all 10.225.41.95/24 md5 | ||
| + | |||
| + | == Describe a table == | ||
| + | You can query the 'information_schema.columns' table to find internal table information. | ||
| + | |||
| + | This is a sample command to find information about a table. | ||
| + | SELECT table_name, column_name, data_type | ||
| + | FROM information_schema.columns | ||
| + | WHERE table_name='TABLE_A'; | ||
| + | |||
| + | == Top 10 rows == | ||
| + | This is to get the top 10 rows. | ||
| + | select * | ||
| + | from table_a | ||
| + | fetch first 10 rows only; | ||
| + | |||
| + | |||
| + | == PSQL VIM == | ||
| + | Tips for editing script and running them in th PSQL command. | ||
| + | |||
| + | 1. Edit a script in vim. Once logged in use the following command. | ||
| + | \e | ||
| + | |||
| + | 2. Multiple windows. | ||
| + | In 1 window connect using the psql tool and run scripts using the following command. | ||
| + | \o ./sql.out \i ./test.sql | ||
| + | |||
| + | In 2 window using VIM, edit the test.sql file and open a new tab of sql.out. | ||
| + | Then refresh the buffer using | ||
| + | :e | ||
Latest revision as of 13:56, 4 November 2024
Contents
Test Database Connection
This is a way to test the database connection.
./pg_isready -h localhost -p 5432
Application Structure
Log location
./data/log/
OSX
The Postgres app live here on OSX
/Library/PostgreSQL/11
Start
sudo -u postgres ./bin/pg_ctl -D ./data start
Stop
sudo -u postgres ./bin/pg_ctl -D ./data stop
FYI, haven't tried but look like a nicer app to control Postgres is https://postgresapp.com.
If you are not using the system registered database you can all simply use the following commands.
pg_ctl -D ./data start pg_ctl -D ./data stop
Initialize a database directory
To initialize a database directory use the following command.
initdb -D <data_directory>
Environment Path
To add PostgreSQL in to your environment path.
export POSTGRESQL_HOME=/Library/PostgreSQL/11 PATH=$PATH:$POSTGRESQL_HOME/bin
Debian
You can start Postgres in Debian using
/etc/init.d/postgresql start
Create a database and user
sudo su - postgres createdb testdb createuser testdb
To create a password for the user either use
createuser testdb --pwprompt
or
psql ALTER USER 'testdb' WITH PASSWORD 'testdb';
To have the new user be a superuser add the following.
createuser --superuser testdb --pwprompt
Run file and commands from CLI
PSQL will ask for your password every time unless you set it as an environment variable.
export PGPASSWORD=Password123
To run a command.
psql -U user_name -d database_name -c "SELECT * FROM tblExample"
To run a file.
psql -U user_name -d database_name -f ./file.sql
Issues
Error Connecting to database FATAL : no pg_hba.conf entry for host I had to add the host ip address into the pg_hba.conf file.
location of file: ./data/pg_hba.conf
Added the following line and then restarted Postgres.
host all all 10.225.41.95/24 md5
Describe a table
You can query the 'information_schema.columns' table to find internal table information.
This is a sample command to find information about a table.
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name='TABLE_A';
Top 10 rows
This is to get the top 10 rows.
select * from table_a fetch first 10 rows only;
PSQL VIM
Tips for editing script and running them in th PSQL command.
1. Edit a script in vim. Once logged in use the following command.
\e
2. Multiple windows. In 1 window connect using the psql tool and run scripts using the following command.
\o ./sql.out \i ./test.sql
In 2 window using VIM, edit the test.sql file and open a new tab of sql.out. Then refresh the buffer using
:e
