Explore information related to postgres


Can't connect to AWS RDS instance - Tips to fix it ?


This article covers methods to resolve "Can't connect to AWS RDS instance" error for our Customers. If either the telnet or nc commands succeed, then a network connection was established. This means the issue is likely caused by the user authentication to the database, such as user name and password.

Read More



PostgreSQL backup failed in Web help desk - Fix it Now ?


This article covers methods to resolve PostgreSQL database and fix PostgreSQL error. It may be necessary to create a backup and restore your Web Help Desk database.

This is how to back up and restore the Web Help Desk 12.x PostgreSQL embedded database. The process involves creating a backup from the web interface and restore using the embedded pgAdmin application on the Web Help Desk.

Read More



PostgreSQL Error code 23505 - Fix it Now ?


This article covers methods to fix PostgreSQL Error code 23505 for our customers. This issue happens when you are trying to insert a value in a column that already exists there. If you have a sequencer on the field (like with a serial column type), it is likely out of sync with the table because you have done an insert and supplied a value (rather than letting the "default" mechanism handle it by getting the nextval from the sequencer). If this is the case, reset the sequencer with a setval statement to the max value of the field.

Read More



PostgreSQL Error code 42703 - Fix it Now ?


This article covers steps to resolve PostgreSQL Error code 42703 for our customers. PostgreSQL database 42703 error triggers an error message "column does not exist" which indicates either that the requested column does not it exist, or that the query is not correct.

There are many possible reasons for this issue.

To get started, check your query for any mistakes. Often, the error is caused by a lack of quotes.

If this is the case, add double quotes to the column name, then try again. 

Read More



pgAdmin "connection to the server has been lost" - Fix it Now ?


This article covers methods to resolve pgAdmin "connection to the server has been lost" error for our customers. When connecting to a PostgreSQL server, you may get this error message. From the error message you will be able to resolve the problem. 


If pgAdmin displays this message, there are two possible reasons for this:

  • the database server isn't running - simply start it.
  • the server isn't configured to accept TCP/IP requests on the address shown.


For security reasons, a PostgreSQL server "out of the box" doesn't listen on TCP/IP ports. Instead, it must be enabled to listen for TCP/IP requests. This can be done by adding listen_addresses=’*’; this will make the server accept connections on any IP interface.

Read More



"FATAL: no pg_hba.conf entry" – How to fix the PostgreSQL error ?


This article covers methods to resolve "FATAL: no pg_hba.conf entry" error. Basically, the "no pg_hba.conf entry" can happen due to missing entries in the configuration file. 


A quick way to fix this PostgreSQL server error is to do the following steps:

1. Add or edit the following line in your postgresql.conf :

listen_addresses = '*'

2. Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

3. Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.

Read More



"psql :could not connect to server: no route to host" - Fix it Now ?


This article covers how to fix the "could not connect to server: No route to host" PostgreSQL error. Basically, the "could not connect to server: No route to host" triggers while connecting to PostgreSQL due to a number of reasons.

Make the Iptables rules different in Master and Slave.

iptables -L INPUT
iptables -P INPUT ACCEPT
iptables -P OUTPUT ACCEPT
iptables -P FORWARD ACCEPT
iptables -F INPUT 
iptables -F OUTPUT
iptables  -F FORWARD

Read More



Allow remote access to phppgadmin - The easy way


This article covers how to Install, configure and enable remote access to phpPgAdmin. Here you will learn how to enable remote access to PostgreSQL server on a Plesk server.


To Access PhpPgAdmin On Linux And MacOS:

1. Open a new terminal window on your local system (for example, using “Finder -> Applications -> Utilities -> Terminal” in macOS or the Dash in Ubuntu).

2. Make sure that you have your SSH credentials (.pem key file) in hand.

3. un the following command to configure the SSH tunnel using the SSH key file. Remember to replace KEYFILE with the path to your private key and SERVER-IP with the public IP address or hostname of your server:

$ ssh -N -L 8888:127.0.0.1:80 -i KEYFILE bitnami@SERVER-IP

Remember that if you are redirecting HTTP requests to the HTTPS port, you must use destination port 443 instead of 80.

4. Access the phpPgAdmin console through the secure SSH tunnel you created, by browsing to http://127.0.0.1:8888/phppgadmin.

5. Log in to phpPgAdmin by using the following credentials:

Username: postgres.
Password: application password.

Read More



WHMCS error code 1013 - Fix it Now ?


This article covers WHMCS error code 1013. Basically, this error happens when the external_id entry is missing in psa.clients MySQL table. If the issue appears on WHMCS integrated with Plesk Multi Server, this database update should be done on the management node side.

Read More



"403 forbidden" error in phppgadmin - Fix it Now ?


This article covers methods to tackle "403 forbidden" error messages in phpPgAdmin. Basically, the "Forbidden" error happens while accessing phpPgAdmin when permissions in the phpPgAdmin configuration file are not intact. 


To resolve 403 forbidden error, In the Apache configuration, simply replace:

<Directory /usr/share/phppgadmin>
    DirectoryIndex index.php
    Order deny,allow
    Allow from all
</Directory>

With:

<Directory /usr/share/phppgadmin>
   Require all granted
</Directory>

Read More



"Login disallowed for security reasons" phppgadmin error – Fix it Now ?


This article covers how to resolve "Login disallowed for security reasons" phppgadmin error. Basically, the "Login disallowed for security reasons" error can happen when extra login security is enabled in the phpPgAdmin configuration file.


To fix this issue, You will need to edit the phppgadmin config file from following directory /etc/phppgadmin/config.inc.php.

Finally modify the line $conf['extra_login_security'] = true; to $conf['extra_login_security'] = false;

Read More



Cloud SQL Proxy error – An attempt was made to access a socket


This article covers methods to resolve Cloud SQL Proxy error. 

The Cloud SQL Proxy error looks like this:

An attempt was made to access a socket in a way forbidden by its access permissions


Therefore you cannot bind to it while it's running:

The SQL Server engages the 3306 port locally. 

1. Stop the SQL Server to bind the CloudSQL Proxy to it.

2. Incase of Windows 10: Go to Task Manager -> Services -> MySQL57

3. Right click and stop that task. 

4. Once you have done that try running the same command again. 

It'll work and show as output:

Listening on 127.0.0.1:3306 for <instance-name>


Requirements for using the Cloud SQL Auth proxy.

To use the Cloud SQL Auth proxy, you must meet the following requirements:

1. The Cloud SQL Admin API must be enabled.

2. You must provide the Cloud SQL Auth proxy with Google Cloud authentication credentials.

3. You must provide the Cloud SQL Auth proxy with a valid database user account and password.

4. The instance must either have a public IPv4 address, or be configured to use private IP.

The public IP address does not need to be accessible to any external address (it does not need to be added as an authorized network address).

Read More



Configure PostgreSQL on Linux in Vesta control panel - How to do it


This article covers how to install and setup PostgreSQL on Vesta Control panel running RHEL, CentOS, Debian, or Ubuntu server. PostgreSQL is an advanced version of SQL which provides support to different functions of SQL like foreign keys, subqueries, triggers, and different user-defined types and functions.


To set up PostgreSQL on a RHEL or CentOS:

1. Install PostgreSQL packages

yum install postgresql postgresql-server postgresql-contrib phpPgAdmin

* If you have remi installed then don't forget to explicitly enable it.

yum install --enablerepo=remi postgresql postgresql-server postgresql-contrib phpPgAdmin


2. Initialize database cluster

service postgresql initdb


3. Download hba configuration

wget http://c.vestacp.com/0.9.8/rhel/pg_hba.conf -O /var/lib/pgsql/data/pg_hba.conf


4.  Start the server

service postgresql start


5. Set oracle user password

su - postgres

psql -c "ALTER USER postgres WITH PASSWORD 'pgp4sw0rd'"

exit


6. Enable pgsql databases support in vesta.

open /usr/local/vesta/conf/vesta.conf and set DB_SYSTEM to 'mysql,pgsql'


7. Register pg instance in control panel

v-add-database-host pgsql localhost postgres pgp4sw0rd


8. Download phpPgAdmin configuration

wget http://c.vestacp.com/0.9.8/rhel/pga.conf -O /etc/phpPgAdmin/config.inc.php

wget http://c.vestacp.com/0.9.8/rhel/httpd-pga.conf -O /etc/httpd/conf.d/phpPgAdmin.conf


9. Restart web server

service httpd restart


To set up PostgreSQL on a Debian or Ubuntu:

1. Install PostgreSQL packages

apt-get install postgresql postgresql-contrib phppgadmin


2. Download hba configuration

wget http://c.vestacp.com/0.9.8/debian/pg_hba.conf -O /etc/postgresql/*/main/pg_hba.conf


3. Restart the server

service postgresql restart


4. Set oracle user password

su - postgres

psql -c "ALTER USER postgres WITH PASSWORD 'pgp4sw0rd'"

exit


5. Enable pgsql databases support in vesta.

open /usr/local/vesta/conf/vesta.conf and set DB_SYSTEM to 'mysql,pgsql'


6. Register pg instance in control panel

v-add-database-host pgsql localhost postgres pgp4sw0rd


7. Download phpPgAdmin configuration

wget http://c.vestacp.com/0.9.8/debian/pga.conf -O /etc/phppgadmin/config.inc.php

wget http://c.vestacp.com/0.9.8/debian/apache2-pga.conf -O /etc/apache2/conf.d/phppgadmin


8. Restart web server

service apache2 restart

Read More



Zabbix tries to connect to the wrong database - Fix it now


This article covers how to fix Zabbix error, when Zabbix tries to connect to the wrong database.

Basically, when Zabbix tries to connect to the wrong database, we can simply resolve it by restarting the service.

Zabbix is an open-source monitoring software tool for diverse IT components, including networks, servers, virtual machines (VMs) and cloud services. Zabbix provides monitoring metrics, among others network utilization, CPU load and disk space consumption.


How do I fix Zabbix server is not running?

Zabbix server error due to problems with the firewall.

1. As a root user, we check and confirm whether the firewall is allowing connection to Zabbix Server port which is 10051. 

2. If not, then we add the following rule in the configuration file /etc/sysconfig/iptables. 

3. Finally, restart the service in order to fix the error.


How to turn off Zabbix server?

To Stop the Service:

1. Use PuTTY to log in as user robomanager to the server where Zabbix is installed.

2. Run the following command to switch to user root: su - root.

3. Run the following command to stop the Zabbix service: systemctl stop zabbix_server.service.


To monitor my zabbix port:

1. In order to test your configuration, access the Monitoring menu and click on the Latest data option. 

2. Use the filter configuration to select the desired hostname. 

3. Click on the Apply button. 

4. You should be able to see the results of your TCP port monitoring using Zabbix.


Where is zabbix config located?

The Zabbix installation process created an Apache configuration file that contains these settings. 

It is located in the directory /etc/zabbix and is loaded automatically by Apache.


How does Zabbix proxy work?

Zabbix proxy is a process that may collect monitoring data from one or more monitored devices and send the information to the Zabbix server, essentially working on behalf of the server. 

All collected data is buffered locally and then transferred to the Zabbix server the proxy belongs to.


Zabbix Server supported DATABASE ENGINE:

Zabbix Server and Proxy support five database engines:

1. IBM DB2

2. MySQL

3. Oracle

4. PostgreSQL

5. SQLite

Read More



Migrate database from MySQL to PostgreSQL using pgLoader


This article covers how to migrate a database using pgLoader with a single #command.
PostgreSQL, also known as “Postgres,” is an open-source relational database management system (RDBMS).
It has seen a drastic growth in popularity in recent years, with many developers and companies migrating their data to Postgres from other database solutions.
The prospect of migrating a database can be intimidating, especially when migrating from one database management system to another.

Facts about pgLoader:
1. pgLoader is an open-source #database migration tool that aims to simplify the process of migrating to #PostgreSQL.
2. It supports #migrations from several file types and RBDMSs — including #MySQL and SQLite — to PostgreSQL.
3. #pgLoader is a flexible tool that can perform a database migration in a single command.
4. With a few configuration tweaks, it can migrate an entire database from one physical machine to another using a secure SSL/TLS connection.

Read More



Django Application with Kubernetes


This article covers deploying a Scalable and Secure Django Application. Kubernetes is a powerful open-source container orchestrator.
Kubernetes is a powerful open-source container orchestrator that automates the deployment, scaling and management of containerized applications.
Kubernetes objects like ConfigMaps and Secrets allow you to centralize and decouple configuration from your containers, while controllers like Deployments automatically restart failed containers and enable quick scaling of container replicas.
TLS encryption is enabled with an Ingress object and the ingress-nginx open-source Ingress Controller.
The cert-manager Kubernetes add-on renews and issues certificates using the free Let’s Encrypt certificate authority.

Read More



Zabbix Postgres vs MySQL


This article will help you give you more informations about #Zabbix Postgres vs MySQL and the criteria to choose between them. 

PostgreSQL is an Object Relational #Database Management System (#ORDBMS) whereas MySQL is a community driven DBMS system. PostgreSQL support modern applications feature like #JSON, XML etc. while MySQL only supports JSON.

PostgreSQL supports Materialized Views whereas #MySQL doesn't supports Materialized Views.

#PostgreSQL is known to be faster while handling massive data sets, complicated queries, and read-write operations. 

Meanwhile, MySQL is known to be faster with read-only commands.

Advantages Of Using MySQL:

1. It's Easy To Use.

2. Support Is Readily Available Whenever Necessary.

3. It's Open-Source.

4. It's Incredibly Inexpensive.

5. It's An Industry Standard.

6. It's Got A Few Stability Issues.

7. It Suffers From Relatively Poor Performance Scaling.

Read More



Copy rds snapshot to s3


This article will guide you on resolve common rds #error. Basically, the process to copy rds snapshot to s3 is simple and creates and saves backups of the DB instance in the Amazon S3 bucket. 

Amazon Relational Database Service (#Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

RDS creates a storage volume snapshot of your #DB instance, backing up the entire DB instance and not just individual databases. RDS saves the automated backups of your DB instance according to the backup retention period that you specify.

To copy a #RDS snapshot from one region to another:

1. Create a #snapshot of your RDS.

2. In your newly created snapshot go to Actions -> Copy Snapshot.

3. Click orange View snapshots in destination region.

4. In the target region's snapshots table find your copied snapshot, and wait until status "Copying" becomes "Completed".

5. Select the snapshot, click Actions -> Restore Snapshot.

Read More



psql to restore a text dump How to do it


This article will guide you on steps to perform  text dump restoration. Basically, psql restores a text dump in the same file format.

You can backup a single table, a #schema, or a complete database. Select the name of the backup source in the #pgAdmin tree control, right click to open the context menu, and select Backup… to open the #Backup dialog. 

The name of the object selected will appear in the dialog title bar.

In #PostgreSQL, you can restore a database in two ways:

i. Using psql to restore plain #SQL script file generated by pg_dump and pg_dumpall tools.

ii. Using pg_restore to restore tar file and directory format created by the pg_dump tool.


To Restore Postgres Database:

1. Restore a postgres database. $ psql -U erp -d erp_devel -f mydb.sql.

2. Backup a local postgres database and restore to remote server using single command: $ pg_dump dbname | psql -h hostname dbname.

3. Restore all the postgres databases.

4. Restore a single postgres table.

Read More



PostgreSQL installation on Centos 7


This article will show you how to install PostgreSQL via the default Centos repositories or from the official Postgres repositories.

Read More




For Linux Tutorials

We create Linux HowTos and Tutorials for Sys Admins. Visit us on LinuxAPT.com

Also for Tech related tips, Visit forum.outsourcepath.com or General Technical tips on www.outsourcepath.com






Focus on your business, not your servers.

Click Here to Learn More