Run the following command to install pgAdmin:. ![]() Update the package lists to synchronize the repository.Sudo sh -c 'echo "deb $(lsb_release -cs) pgadmin4 main" > /etc/apt//pgadmin4.list' Run these commands individually to add the pgAdmin official repository:Ĭurl -fsSL | sudo gpg -dearmor -o /etc/apt//pgadmin.gpg.We will use the APT package manager to install it: Install the pgAdmin web-based GUI to simplify your PostgreSQL database management tasks. # Connect your app to the remote database.Ĭonn = nnect(host="182.182.182.182", port="5432", dbname="TestNewUser", user="TestNewUser", password="UserPassword") Remember to change the value accordingly: Add the following line to your application code to import the module and establish a connection with the PostgreSQL database.Download and install the psycopg2 database driver using pip:.Install the pip package manager using this command.After connecting via SSH, follow these steps: To enable PostgreSQL remote access in your application, install a database driver and import the module into your code.įor this tutorial, we will show you how to do so in a Python application hosted in an Ubuntu 20.04 VPS. Once connected, you can send database queries to retrieve data. To verify if the connection is successful, run this command: \conninfo Remember that the default PostgreSQL port is 5423. To do so, use this command syntax in the client machine: psql -h ip_address -p port -d database -U usernameįor example, run this command to connect to the TheNewUser database hosted in a VPS with the 185.185.185.185 IP address using the TheNewUser account: psql -h 185.185.185.185 -p 5432 -d TheNewUser -U TheNewUser Restart the PostgreSQL service to apply the changes.Īfter configuring the database, set up remote access to PostgreSQL using the psql command.These users can use any IP address with an MD5-encrypted password. For instance, we will enable all TCP/IP connections from all users to every PostgreSQL database.Add a new policy at the bottom of the file using this syntax:Ĭonnection database user ip_address encryption.To do so, open the pg_hba.conf file in the same directory using vim. Set the access policy to authenticate client connection to your PostgreSQL server.Use the :wq command to save the changes and quit vim.Remove the # symbol and change the value to an asterisk.Replace vim if you use another editor and change the directory accordingly: Open the configuration file using a text editor.To do so, let the server listen to all IP addresses by editing the configuration file.īefore proceeding, use the exit command to quit the PostgreSQL shell and return as the regular system user. Configure the PostgreSQL ServerĬonfigure your PostgreSQL server to allow other systems to access the database remotely. To show the table, enter the following statement: SELECT * FROM tablename 7. Don’t forget to replace the placeholders with the appropriate value and adjust the number of rows accordingly: INSERT INTO tablename (column1, column2, column3) VALUES (row1, row2, row3) To add values to your table, use the following statement. EXCLUSION – prevents values in two rows from overlapping when compared. ![]() UNIQUE – guarantees that the values in a column are unique among all rows.CHECK – sets a column’s value so that it must satisfy a Boolean expression, which is either “true” or “false”.NOT NULL – ensures a column’s value is not empty.FOREIGN KEY – specifies that the values in a column must be the same as the ones in another table’s rows.PRIMARY KEY – indicates that a column can be a row’s unique identifier.When creating a database table, consider the six PostgreSQL constraints: For example, enter the following to create a buyer data table: CREATE TABLE buyers(usr_id INT PRIMARY KEY, usr_name VARCHAR(240) NOT NULL, usr_location VARCHAR(240) NOT NULL) The user should now connect to the new database and be able to create a new table. Enter the PostgreSQL version query statement:.To confirm the installation, run this command:.Install the latest version of PostgreSQL with APT and sudo privileges:.Refresh APT to synchronize all repositories using this command:.Sudo sh -c 'echo "deb $(lsb_release -cs)-pgdg main" > /etc/apt//pgdg.list' ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |